如何将行值转换为具有动态列数的列?
我有以下数据:
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 的唯一序列.
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;
参见演示.此查询将生成新列名称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 |
然后您可以使用 seq 中列出的新列名在 columnC 上应用数据透视:
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) |
相关文章