检查这个小提琴
;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值，则可以尝试以下操作：
这是代码：
;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
602392714
清零编程群