本文介紹了sql server 單行多列合并為一列的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我有這樣的桌子
Reg_No Student_Name Subject1 Subject2 Subject3 Subject4 Total
----------- -------------------- ----------- ----------- ----------- ----------- -----------
101 Kevin 85 94 78 90 347
102 Andy 75 88 91 78 332
從這里我需要創建一個臨時表或這樣的表:
From this I need to create a temp table or table like this:
Reg_No Student_Name Subject Total
----------- -------------------- ----------- -----------
101 Kevin 85 347
94
78
90
102 Andy 75 332
88
91
78
有沒有辦法在 SQL Server 中做到這一點?
Is there a way I can do this in SQL Server?
推薦答案
檢查 this Fiddle
;WITH MyCTE AS
(
SELECT *
FROM (
SELECT Reg_No,
[Subject1],
[Subject2],
[Subject3],
[Subject4]
FROM Table1
)p
UNPIVOT
(
Result FOR SubjectName in ([Subject1], [Subject2], [Subject3], [Subject4])
)unpvt
)
SELECT T.Reg_No,
T.Student_Name,
M.SubjectName,
M.Result,
T.Total
FROM Table1 T
JOIN MyCTE M
ON T.Reg_No = M.Reg_No
如果您確實想要其余的 NULL 值，您可以嘗試以下操作:
If you do want NULL values in the rest, you may try the following:
這是新的小提琴
這是代碼:
;WITH MyCTE AS
(
SELECT *
FROM (
SELECT Reg_No,
[Subject1],
[Subject2],
[Subject3],
[Subject4]
FROM Table1
)p
UNPIVOT
(
Result FOR SubjectName in ([Subject1], [Subject2], [Subject3], [Subject4])
)unpvt
),
MyNumberedCTE AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Reg_No ORDER BY Reg_No,SubjectName) AS RowNum
FROM MyCTE
)
SELECT T.Reg_No,
T.Student_Name,
M.SubjectName,
M.Result,
T.Total
FROM MyCTE M
LEFT JOIN MyNumberedCTE N
ON N.Reg_No = M.Reg_No
AND N.SubjectName = M.SubjectName
AND N.RowNum=1
LEFT JOIN Table1 T
ON T.Reg_No = N.Reg_No
這篇關于sql server 單行多列合并為一列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！