谈SQL Server 2005中的T-SQL增强(7)

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