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

[qq_31680453]

如图数据库结构，希望通过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)