sql-server - 如何将行值转换为具有动态列数的列？

Question

我有以下数据:

DECLARE @DataSource TABLE
(
     [ColumnA] INT
    ,[ColumnB] INT
    ,[ColumnC] INT
)

INSERT INTO @DataSource ([ColumnA], [ColumnB], [ColumnC])
VALUES   (5060,1006,100118)
        ,(5060,1006,100119)
        ,(5060,1006,100120)
        ,(5060,1007,100121)
        ,(5060,1007,100122)
        ,(5060,1012,100123)

SELECT [ColumnA]
      ,[ColumnB]
      ,[ColumnC]
FROM @DataSource

我需要像这样转换:



困难的部分是数据是动态的(我不知道我将有多少列)并且我无法在这里使用标准数据透视表，因为 ColumnC 中的值是不同的，因此我将拥有与 ColumnC 中出现的值一样多的列.

有什么技术可以实现这一目标吗？
任何形式的帮助(答案、文章、建议)将不胜感激。

Answer 1

每当您使用 PIVOT 时，我的建议是始终首先使用硬编码的值编写查询，然后您可以轻松地将查询转换为动态解决方案。

由于您将有多个值 columnC将转换为列，那么您需要查看使用 row_number()为每个 columnc 生成唯一序列的窗口函数基于 columnA 的值和 columnB .

您的查询的起点将是:

select [ColumnA],
  [ColumnB],
  [ColumnC],
  'SampleTitle'+
  cast(row_number() over(partition by columna, columnb
                          order by columnc) as varchar(10)) seq
from DataSource;

见 Demo .此查询将生成新列名称列表 SampleTitle1 ， 等等:

| COLUMNA | COLUMNB | COLUMNC |          SEQ |
|---------|---------|---------|--------------|
|    5060 |    1006 |  100118 | SampleTitle1 |
|    5060 |    1006 |  100119 | SampleTitle2 |
|    5060 |    1006 |  100120 | SampleTitle3 |

然后，您可以在 columnC 上应用枢轴新列名列在 seq 中:

select columnA, columnB, 
  SampleTitle1, SampleTitle2, SampleTitle3
from
(
   select [ColumnA],
    [ColumnB],
    [ColumnC],
    'SampleTitle'+
      cast(row_number() over(partition by columna, columnb
                              order by columnc) as varchar(10)) seq
   from DataSource
) d
pivot
(
  max(columnc)
  for seq in (SampleTitle1, SampleTitle2, SampleTitle3)
) piv;

见 SQL Fiddle with Demo .

一旦有了正确的逻辑，就可以将数据转换为动态 SQL。这里的关键是生成新列名的列表。我通常使用 FOR XML PATH为此类似于:

select STUFF((SELECT distinct ',' + QUOTENAME(seq) 
                from
                (
                  select 'SampleTitle'+
                    cast(row_number() over(partition by columna, columnb
                                            order by columnc) as varchar(10)) seq
                  from DataSource
                ) d
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

见 Demo .获得列名列表后，您将生成要执行的 sql 字符串，完整代码将是:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(seq) 
                    from
                    (
                      select 'SampleTitle'+
                        cast(row_number() over(partition by columna, columnb
                                                order by columnc) as varchar(10)) seq
                      from DataSource
                    ) d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT columnA, ColumnB,' + @cols + ' 
             from 
             (
               select [ColumnA],
                [ColumnB],
                [ColumnC],
                ''SampleTitle''+
                  cast(row_number() over(partition by columna, columnb
                                          order by columnc) as varchar(10)) seq
               from DataSource
            ) x
            pivot 
            (
                max(columnc)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

见 SQL Fiddle with Demo .这些给出了一个结果:

| COLUMNA | COLUMNB | SAMPLETITLE1 | SAMPLETITLE2 | SAMPLETITLE3 |
|---------|---------|--------------|--------------|--------------|
|    5060 |    1006 |       100118 |       100119 |       100120 |
|    5060 |    1007 |       100121 |       100122 |       (null) |
|    5060 |    1012 |       100123 |       (null) |       (null) |