SQL Server 单行多列合并为一列

Question

我有这样一张表格：

Reg_No      Student_Name         Subject1    Subject2    Subject3    Subject4    Total
----------- -------------------- ----------- ----------- ----------- ----------- -----------
101         Kevin                85          94          78          90          347
102         Andy                 75          88          91          78          332

我需要创建一个临时表或类似这样的表：

Reg_No      Student_Name         Subject     Total
----------- -------------------- ----------- -----------
101         Kevin                85          347
                                 94           
                                 78           
                                 90           
102         Andy                 75          332
                                 88           
                                 91           
                                 78           

有没有一种方法可以在SQL Server中实现这个？

- Mathi

---

你在问题中所有的HTML格式都是手动添加的吗？如果你想发布表格数据并保留格式，你可以只需选择该块并点击“{}”按钮即可（我承认我自己也不愿删除所有这些格式）。 - Damien_The_Unbeliever

你是否在寻找同一组行中的空白字段？ - Francesco De Lisi

@Damien_The_Unbeliever - 我也注意到了。对 OP 的努力加一。 - Lieven Keersmaekers

这么多 不错的努力。 - Onel Sarmiento

@Mathi 如果你只想在HTML中显示结果...那么只需使用<br/>标签连接主题列，然后将结果数据集表绑定到你的网格上...你将得到所需的相同结果。 - Vishal Patel

Answer 1

DDL:

DECLARE @temp TABLE
(
      Reg_No INT
    , Student_Name VARCHAR(20)
    , Subject1 INT
    , Subject2 INT
    , Subject3 INT
    , Subject4 INT
    , Total INT
)

INSERT INTO @temp (Reg_No, Student_Name, Subject1, Subject2, Subject3, Subject4, Total)
VALUES 
    (101, 'Kevin', 85, 94, 78, 90, 347),
    (102, 'Andy ', 75, 88, 91, 78, 332)

Query #1 - ROW_NUMBER:

SELECT  Reg_No = CASE WHEN rn = 1 THEN t.Reg_No END
    ,   Student_Name = CASE WHEN rn = 1 THEN t.Student_Name END
    ,   t.[Subject]
    ,   Total = CASE WHEN rn = 1 THEN t.Total END
FROM (
    SELECT 
          Reg_No
        , Student_Name
        , [Subject]
        , Total 
        , rn = ROW_NUMBER() OVER (PARTITION BY Reg_No ORDER BY 1/0)
    FROM @temp
    UNPIVOT 
    (
        [Subject] FOR tt IN (Subject1, Subject2, Subject3, Subject4)
    ) unpvt
) t

查询 #2 - OUTER APPLY：

SELECT t.*
FROM @temp
OUTER APPLY 
(
    VALUES 
        (Reg_No, Student_Name, Subject1, Total),
        (NULL, NULL, Subject2, NULL),
        (NULL, NULL, Subject3, NULL),
        (NULL, NULL, Subject4, NULL)
) t(Reg_No, Student_Name, [Subject], Total)

查询计划：

查询成本：

输出结果：

Reg_No      Student_Name         Subject     Total
----------- -------------------- ----------- -----------
101         Kevin                85          347
NULL        NULL                 94          NULL
NULL        NULL                 78          NULL
NULL        NULL                 90          NULL
102         Andy                 75          332
NULL        NULL                 88          NULL
NULL        NULL                 91          NULL
NULL        NULL                 78          NULL

提示： 在您的情况下，使用 OUTER APPLY 查询比使用 ROW_NUMBER 方案更快。

- Devart

Answer 2

最简单的方法是使用UNION子句。

select Reg_No, Student_Name, Subject1, Total from YourTable union all
select Reg_No, Student_Name, Subject2, Total from YourTable union all
select Reg_No, Student_Name, Subject3, Total from YourTable union all
select Reg_No, Student_Name, Subject3, Total from YourTable

UNION

将两个或多个查询结果合并为一个结果集，其中包括属于联合中所有查询的行。UNION操作与使用连接组合来自两个表的列不同。

以下是使用UNION组合两个查询结果集的基本规则：

• 所有查询中的列数和顺序必须相同。

• 数据类型必须兼容。

- Lieven Keersmaekers

---

也许你想在获取Subject2、3等行中选择nulls而不是reg_no、student_name等。 - Roman Pekar

@RomanPekar - 如果OP确实需要问题中提出的内容，那么你是正确的，但他将如何从那个表中进行选择呢？我认为需要一个额外的键来使事情变得复杂。 - Lieven Keersmaekers

我尝试了UNION，但是只有UNION ALL才能解决我的问题。这让我省了很多事，谢谢！ - Zakk Diaz

Answer 3

检查这个 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值，可以尝试以下方法： 这是新的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
),
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

- Giannis Paraskevopoulos

---

这并不是 OP 想要的。轴部分没问题，但他还想“清除”所有重复的值。 - Tim Schmelter

@TimSchmelter，这并没有明确提到。仅仅是通过请求的结果隐含了。 - Giannis Paraskevopoulos

@TimSchmelter 我已经编辑了我的答案并提供了另一种解决方案，如果你想要检查的话 :) - Giannis Paraskevopoulos

如果Reg_No，Student_Name为NULL或为空，那么如何获取Subject2，Subject3，Subject4的值？是否可以通过使用Reg_No来访问这些值？ - Mathi

如果您想要这个，请检查我的第一个程序。请检查第一个fiddle。 - Giannis Paraskevopoulos

Answer 4

你应该关注 PIVOT 运算符：

http://technet.microsoft.com/en-us/library/ms177410(v=sql.100).aspx

- Nicolas C.

Answer 5

> DECLARE  @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
> 
>  select @cols = STUFF((SELECT ',' + QUOTENAME(designation) 
>                      from MyTable
>                      group by designation  
>                      order by designation
>              FOR XML PATH(''), TYPE
>              ).value('.', 'NVARCHAR(MAX)'),1,1,'') 
> 
>  set @query = N'SELECT Row, ' + @cols + N' from 
>               (
>                  select ''SS'' Row, SS AS Value , designation from MyTable
>                  UNION ALL
>                  select  ''AS'' Row, [AS] AS Value , designation from MyTable
>                  UNION ALL 
>                  select  ''Vac'' Row, Vac AS Value , designation from MyTable 
>              ) x
>              pivot 
>              (
>                  max(Value) for designation in (' + @cols + N')
>              ) p '                    
>      exec sp_executesql @query;

更多细节请参见：当行数不固定时将行转换为列

- Shailendra Mishra