如何将行值转换为具有动态列的列计数？ [英] How to convert row values to columns with dynamic columns count?

问题描述

我有以下数据：

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 中显示值的列数。

The difficult part is that the data is dynamic (I do not know how many columns I will have) and I am not able to use a standard pivot here because the values in ColumnC are different and as a result I am going to have as many columns as values appears in ColumnC.

是否有任何技术来实现这一点？
任何一种帮助（答案，文章，建议）将不胜感激。

Is there any technique to achieve this? Any kind of help (answers, articles, suggestions) will be appreciated.

推荐答案

使用PIVOT将首先写入查询的值为硬编码，然后可以轻松地将查询转换为动态解决方案。

My suggestion whenever you are working with PIVOT is to alway write the query first with the values hard-coded, then you can easily convert the query to a dynamic solution.

由于您将拥有多个将要转换为列的 columnC 的值，则需要使用 row_number（）窗口函数根据 columnA 和 columnB columnc 生成唯一的序列c $ c>。

Since you are going to have multiple values of columnC that will be converted to columns, then you need to look at using the row_number() windowing function to generate a unique sequence for each columnc based on the values of columnA and 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 等：

See Demo. This query will generate the list of new columns names SampleTitle1, etc:

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

然后您可以在 columnC 新列名称列在 seq 中：

You can then apply the pivot on columnC with the new column names listed in 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 ，类似于：

Once you have the correct logic, you can convert the data to dynamic SQL. The key here is generating the list of new column names. I typically use FOR XML PATH for this similar to:

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,'')

请参阅演示。一旦你有列名列表，那么你将生成你的sql字符串来执行，完整的代码将是：

See Demo. Once you have the list of column names, then you will generate your sql string to execute, the full code will be:

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 。这些结果如下：

See SQL Fiddle with Demo. These give a result:

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

这篇关于如何将行值转换为具有动态列的列计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！