如果要将
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;
