谈SQL Server 2005中的T-SQL增强E

新的关系运算符 PIVOT/UNPIVOT/APPLY

 

 

1、PIVOT

 

 

PIVOT运算符将行旋转为列，并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者是CTE只返回所关注的列。

 

 

2、UNPIVOT

 

 

UNPIVOT运算符执行与PIVOT运算符相反的操作；他将列旋转为行了。

 

 

3、APPLY

 

 

APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY，其方式与使用JOIN关系运算符类似。APPLY具有两种形式：CROSS APPLY和OUTER APPLY。

 

 

演示：

 

USE demo GO CREATE TABLE orders ( Customer VARCHAR(10) NOT NULL, product VARCHAR(20) NOT NULL, quantity INT NOT NULL ) GO INSERT orders VALUES('Mike', 'Bike',3) INSERT orders VALUES('Mike','Chain',2) INSERT orders VALUES('Mike','Bike',5) INSERT orders VALUES('Lisa','Bike',3) INSERT orders VALUES('Lisa','Chain',3) INSERT orders VALUES('Lisa','Chain',4) INSERT orders VALUES('Lisa','Bike',2) SELECT * FROM orders SELECT * FROM orders PIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS a USE demo GO CREATE TABLE SALES1 ( [Year] INT, Quarter CHAR(2), Amount FLOAT ) GO INSERT INTO SALES1 VALUES (2001, 'Q1', 80) INSERT INTO SALES1 VALUES (2001, 'Q2', 70) INSERT INTO SALES1 VALUES (2001, 'Q3', 55) INSERT INTO SALES1 VALUES (2001, 'Q3', 110) INSERT INTO SALES1 VALUES (2001, 'Q4', 90) INSERT INTO SALES1 VALUES (2002, 'Q1', 200) INSERT INTO SALES1 VALUES (2002, 'Q2', 150) INSERT INTO SALES1 VALUES (2002, 'Q2', 40) INSERT INTO SALES1 VALUES (2002, 'Q2', 60) INSERT INTO SALES1 VALUES (2002, 'Q3', 120) INSERT INTO SALES1 VALUES (2002, 'Q3', 110) INSERT INTO SALES1 VALUES (2002, 'Q4', 180) GO SELECT * FROM SALES1 PIVOT (SUM (Amount) --使用SUM聚合数量列 FOR [Quarter] --PIVOT Quarter 列 IN (Q1, Q2, Q3, Q4)) --使用季节 AS P GO SELECT * INTO temp1 FROM orders PIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS a SELECT * FROM temp1 SELECT customer, product,quantity FROM temp1 UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a ---------------------------------------------------- USE demo GO CREATE TABLE Arrays ( aid INT NOT NULL IDENTITY PRIMARY KEY, array VARCHAR(7999) NOT NULL ) GO INSERT INTO Arrays VALUES('') INSERT INTO Arrays VALUES('10') INSERT INTO Arrays VALUES('20,40,30') INSERT INTO Arrays VALUES('-1,-3,-5') GO CREATE FUNCTION function1(@arr AS VARCHAR(7999)) RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL) AS BEGIN DECLARE @end AS INT, @start AS INT, @pos AS INT SELECT @arr = @arr + ',', @pos = 1, @start = 1, @end = CHARINDEX(',', @arr, @start) WHILE @end > 1 BEGIN INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start)) SELECT @pos = @pos + 1, @start = @end + 1, @end = CHARINDEX(',', @arr, @start) END RETURN END --测试 SELECT * FROM function1('200,400,300') GO SELECT A.aid, F.* FROM Arrays AS A CROSS APPLY function1(array) AS F GO SELECT A.aid, F.* FROM Arrays AS A OUTER APPLY function1(array) AS F GO

 

USE demo
GO
CREATE TABLE orders
(
Customer VARCHAR(10) NOT NULL,
product VARCHAR(20) NOT NULL,
quantity INT NOT NULL
)
GO
INSERT orders VALUES('Mike', 'Bike',3)
INSERT orders VALUES('Mike','Chain',2)
INSERT orders VALUES('Mike','Bike',5)
INSERT orders VALUES('Lisa','Bike',3)
INSERT orders VALUES('Lisa','Chain',3)
INSERT orders VALUES('Lisa','Chain',4)
INSERT orders VALUES('Lisa','Bike',2)
SELECT * FROM orders
SELECT * FROM orders
PIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS a
USE demo
GO
CREATE TABLE SALES1
(
[Year] INT,
Quarter CHAR(2),
Amount FLOAT
)
GO
INSERT INTO SALES1 VALUES (2001, 'Q1', 80)
INSERT INTO SALES1 VALUES (2001, 'Q2', 70)
INSERT INTO SALES1 VALUES (2001, 'Q3', 55)
INSERT INTO SALES1 VALUES (2001, 'Q3', 110)
INSERT INTO SALES1 VALUES (2001, 'Q4', 90)
INSERT INTO SALES1 VALUES (2002, 'Q1', 200)
INSERT INTO SALES1 VALUES (2002, 'Q2', 150)
INSERT INTO SALES1 VALUES (2002, 'Q2', 40)
INSERT INTO SALES1 VALUES (2002, 'Q2', 60)
INSERT INTO SALES1 VALUES (2002, 'Q3', 120)
INSERT INTO SALES1 VALUES (2002, 'Q3', 110)
INSERT INTO SALES1 VALUES (2002, 'Q4', 180)
GO
SELECT * FROM SALES1
PIVOT
(SUM (Amount) --使用SUM聚合数量列
FOR [Quarter] --PIVOT Quarter 列
IN (Q1, Q2, Q3, Q4)) --使用季节
AS P
GO
SELECT * INTO temp1 FROM orders
PIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS a
SELECT * FROM temp1
SELECT customer, product,quantity
FROM temp1
UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a
----------------------------------------------------
USE demo
GO
CREATE TABLE Arrays
(
aid INT NOT NULL IDENTITY PRIMARY KEY,
array VARCHAR(7999) NOT NULL
)
GO
INSERT INTO Arrays VALUES('')
INSERT INTO Arrays VALUES('10')
INSERT INTO Arrays VALUES('20,40,30')
INSERT INTO Arrays VALUES('-1,-3,-5')
GO
CREATE FUNCTION  function1(@arr AS VARCHAR(7999))
RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
AS
BEGIN
DECLARE @end AS INT, @start AS INT, @pos AS INT
SELECT @arr = @arr + ',', @pos = 1,
@start = 1, @end = CHARINDEX(',', @arr, @start)
WHILE @end > 1
BEGIN
INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))
SELECT @pos = @pos + 1,
@start = @end + 1, @end = CHARINDEX(',', @arr, @start)
END
RETURN
END
--测试
SELECT * FROM function1('200,400,300')
GO
SELECT A.aid, F.*
FROM Arrays AS A
CROSS APPLY function1(array) AS F
GO
SELECT A.aid, F.*
FROM Arrays AS A
OUTER APPLY function1(array) AS F
GO