SQL Server中的行列转换

行列转换

老一点的写法

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)
INSERT INTO SALES1 VALUES (2002, ‘Q5’, 380)
GO

SELECT * FROM SALES1
PIVOT
(SUM (Amount) –使用SUM聚合数量列
FOR [Quarter] –PIVOT Quarter 列
IN (Q1, Q2, Q3, Q4, Q5)) –使用季节
AS P
GO

新一点的写法

declare @SALES1 table
(
  [Year] date,
  Quarter varchar(20),
  Amount varchar(5)
)

INSERT INTO @SALES1 VALUES ('2008-8-8', '拜仁', '胜')
,('2008-8-9', '奇才', '胜')
,('2008-8-9', '湖人', '胜')
,('2008-8-10', '拜仁', '负')
,('2008-8-8', '拜仁', '负')
,('2008-8-12', '奇才', '胜')
,('2008-8-12', '奇才', '平')

SELECT * FROM @SALES1

SELECT * FROM @SALES1
PIVOT
(count (year) --使用SUM聚合数量列
FOR Amount --PIVOT Quarter 列
IN (胜,负,平)) --使用季节
AS P