SQL复杂动态数据透视2

SQL复杂动态数据透视2

如果要将

sequence

数字包括在列名中，则仍然需要先取消透视

col1

和

col2

列，然后再应用透视。所不同的是，您会将

sequence

数字连接到在取消透视过程中创建的列名。

对于已知数量的值，查询将为：

select REFID,     [1col1], [2col1], [3col1],    [1col2], [2col2], [3col2]from (    select REFID,         col = cast(Sequence as varchar(10))+ col, value    from yourtable    cross apply    (        select 'COL1', col1 union all        select 'COL2', col2    ) c (col, value)) dpivot(    max(value)    for col in ([1col1], [2col1], [3col1],     [1col2], [2col2], [3col2])) pivorder by refid;

然后，如果您有未知的数字，则动态SQL版本将为：

DECLARE @cols AS NVARCHAr(MAX),    @query  AS NVARCHAr(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(cast(Sequence as varchar(10))+ col)          from yourtable         cross apply         (  select 'Col1', 1 union all  select 'Col2', 2         ) c(col, so)         group by Sequence, col, so         order by  so, sequence         FOR XML PATH(''), TYPE ).value('.', 'NVARCHAr(MAX)')         ,1,1,'')set @query = 'SELECT refid, ' + @cols + '  from  (    select REFID,          col = cast(Sequence as varchar(10))+ col, value     from yourtable     cross apply     (         select ''COL1'', col1 union all         select ''COL2'', col2     ) c (col, value) ) x pivot  (     max(value)     for col in (' + @cols + ') ) p  order by refid'execute sp_executesql @query;