表格行列轉換

CREATE TABLE #test(id INT,name VARCHAR(20),quarter NVARCHAR(1),profile INT)
 
INSERT INTO #test VALUES(1,'a','1',1000)
INSERT INTO #test VALUES(1,'a','2',2000)
INSERT INTO #test VALUES(1,'a','3',4000)
INSERT INTO #test VALUES(1,'a','4',5000)
INSERT INTO #test VALUES(2,'b','1',3000)
INSERT INTO #test VALUES(2,'b','2',3500)
INSERT INTO #test VALUES(2,'b','3',4200)
INSERT INTO #test VALUES(2,'b','4',5500)
 
SELECT * FROM #test
 
SELECT id,name,
[1] AS "第一季",
[2] AS "第二季",
[3] AS "第三季",
[4] AS "第四季"
FROM #test
pivot
(
SUM(profile)
FOR quarter in ([1],[2],[3],[4])
)
AS pvt