sql

[hhhttt_]

想要得到的結果

[二月十六]

如果col2的数据能确定，可以直接这样写：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([col1] nvarchar(22),[col2] nvarchar(22))
Insert #T
select N'aa',N'c1' union all
select N'bb',N'c2' union all
select N'cc',N'c3' union all
select N'dd',N'c1'
Go
--测试数据结束
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY col2 ORDER BY col1) AS num FROM #T
)
SELECT  MAX(CASE WHEN col2 = 'c1' THEN col1
                 ELSE ''
            END) AS c1 ,
        MAX(CASE WHEN col2 = 'c2' THEN col1
                 ELSE ''
            END) AS c2 ,
        MAX(CASE WHEN col2 = 'c3' THEN col1
                 ELSE ''
            END) AS c3
FROM    cte
GROUP BY num

[二月十六]

如果col2的数据不确定，可以使用动态拼接的方法

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([col1] nvarchar(22),[col2] nvarchar(22))
Insert #T
select N'aa',N'c1' union all
select N'bb',N'c2' union all
select N'cc',N'c3' union all
select N'dd',N'c1'
Go
--测试数据结束
declare @sql varchar(8000)
declare @sql1 varchar(8000)=''
set @sql=';WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY col2 ORDER BY col1) AS num FROM #T
)
SELECT '
select @sql1=@sql1+', max(case col2 when '''+ col2 +''' then col1 else '''' end) ['+ col2 +']'
from (select distinct col2 from #T) as a
set @sql=@sql+STUFF(@sql1,1,1,'')+' from cte group by num'
EXEC(@sql)