请教一个关于SQL行列转置的问题。

--测试数据

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)