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) --Aggregate the Amount column using SUM
FOR [Quarter] --Pivot the Quarter column into column headings
IN (Q1, Q2, Q3, Q4)) --use these quarters
AS P
GO
Year Quarter Amount
----------- ------- ----------------------
2001 Q1 80
2001 Q2 70
2001 Q3 55
2001 Q3 110
2001 Q4 90
2002 Q1 200
2002 Q2 150
2002 Q2 40
2002 Q2 60
2002 Q3 120
2002 Q3 110
2002 Q4 180
(12 行受影响)
Year Q1 Q2 Q3 q4
----------- ---------------------- ---------------------- ---------------------- ----------------------
2001 80 70 165 90
2002 200 250 230 180
(2 行受影响)