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 SALES 1 PIVOT(SUM(Amount)–SUM集約数列FOR[Quarter]–PIVOT Quarter列IN(Q 1,Q 2,Q 3,Q 4,Q 5)–季節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