我試圖獲得以下輸出，但 SQL 只允許我一次執行一個值。
這是我當前使用資料透視表及其回傳的以下輸出的 SQL 代碼。
create table temp
(
PID int,
category varchar(max),
score int,
finalscore int,
)
insert into temp values (1, 'A', 50,20)
insert into temp values (1, 'B', 30,10)
insert into temp values (1, 'C', 40,20)
insert into temp values (1, 'D', 60,70)
insert into temp values (1, 'E', 55,25)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' QUOTENAME(c.category)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query='select PID,' @COLS ' from
(
select [PID],[Score],category from temp
)x
pivot
(
max([Score]) for category in(' @COLS '))p
'
exec (@query)
drop table temp
uj5u.com熱心網友回復：
您可以通過在旋轉資料之前取消旋轉資料來做到這一點，即
SELECT upvt.PID, upvt.category, upvt.Type, upvt.Value
FROM temp AS t
UNPIVOT ([Value] FOR [Type] IN (Score, FinalScore)) AS upvt
Which gives:
| PID | 類別 | 型別 | 價值 |
|---|---|---|---|
| 1 | 一種 | 分數 | 50 |
| 1 | 一種 | 總分 | 20 |
| 1 | 乙 | 分數 | 30 |
| 1 | 乙 | 總分 | 10 |
然后這個額外的Type列將來自您的資料透視表中的額外行。一個完整的例子是：
IF OBJECT_ID(N'tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (PID INT, category VARCHAR(MAX), score INT, finalscore INT, );
INSERT INTO #temp (PID, category, score, finalscore)
VALUES
(1, 'A', 50, 20),
(1, 'B', 30, 10),
(1, 'C', 40, 20),
(1, 'D', 60, 70),
(1, 'E', 55, 25);
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' QUOTENAME(c.category)FROM #Temp AS C FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SET @query = N'select PID, [Type], ' @cols N' from
(
SELECT upvt.PID, upvt.category, upvt.Type, upvt.Value
FROM #temp AS t
UNPIVOT ([Value] FOR [Type] IN (Score, FinalScore)) AS upvt
)x
PIVOT (MAX([Value]) FOR category IN (' @cols N'))p
';
EXECUTE sp_executesql @query;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/366931.html
標籤：sql sql-server