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