22,300
社区成员




--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[采购商] nvarchar(21),[商品] nvarchar(23))
Insert #T
select 1,N'A',N'橘子' union all
select 2,N'B',N'西瓜' union all
select 3,N'A',N'葡萄' union all
select 4,N'B',N'哈密瓜' union all
select 5,N'B',N'橘子' union all
select 6,N'C',N'山竹' union all
select 7,N'A',N'芒果' union all
select 8,N'A',N'香蕉'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = '
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 采购商 ORDER BY ID) rn FROM #T
)
select 采购商'
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 采购商 ORDER BY ID) rn FROM #T
)
SELECT @sql = @sql + ',max(case rn when ' + RTRIM(a.rn)
+ ' then 商品 else null end)[商品' + RTRIM(a.rn) + ']'
FROM ( SELECT DISTINCT
rn
FROM cte
) a
SET @sql = @sql
+ ' from cte group by 采购商'
EXEC(@sql)