行转列求助

[一只小小菜鸟]

如何将上图转成

[还在加载中灬]

我假设你的四列分别是[商品],[机构],[数量],[金额] 你参考下

SELECT
	[商品]
	,MAX(CASE[机构]WHEN'机构1'THEN[机构]END)[机构]
	,MAX(CASE[机构]WHEN'机构1'THEN[数量]END)[数量]
	,MAX(CASE[机构]WHEN'机构1'THEN[金额]END)[金额]
	,MAX(CASE[机构]WHEN'机构2'THEN[机构]END)[机构]
	,MAX(CASE[机构]WHEN'机构2'THEN[数量]END)[数量]
	,MAX(CASE[机构]WHEN'机构2'THEN[金额]END)[金额]
	,MAX(CASE[机构]WHEN'机构3'THEN[机构]END)[机构]
	,MAX(CASE[机构]WHEN'机构3'THEN[数量]END)[数量]
	,MAX(CASE[机构]WHEN'机构3'THEN[金额]END)[金额]
FROM
	TB
GROUP BY
	[商品]

如果有机构4呢？是再横过去吗? 如果是的话，建议在程序上处理，在SQL处理很不划算

[还在加载中灬]

动态行转列

DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT[商品]'
SELECT @SQL=@SQL+',MAX(CASE[机构]WHEN'''+[机构]+'''THEN[机构]END)[机构]'
		+',MAX(CASE[机构]WHEN'''+[机构]+'''THEN[数量]END)[数量]'
		+',MAX(CASE[机构]WHEN'''+[机构]+'''THEN[金额]END)[金额]'
	FROM(SELECT[机构]FROM TB GROUP BY[机构])T
SET @SQL=@SQL+'FROM TB GROUP BY[商品]'
EXEC(@SQL)

[中国风]

DECLARE @s NVARCHAR(MAX)
   ,@Rows INT
SELECT TOP 1
        @Rows = COUNT(1)
FROM    表
GROUP BY 商品
ORDER BY COUNT(1) DESC
SET @s = ''
WHILE @Rows > 0 
    SELECT  @s = ',[机构' + RTRIM(@Rows) + ']=max(case when [Rows]='
            + RTRIM(@Rows) + ' then [机构] else '''' end),[数量' + RTRIM(@Rows)
            + ']=max(case when [Rows]=' + RTRIM(@Rows)
            + ' then [数量] else 0 end),[金额' + RTRIM(@Rows)
            + ']=sum(case when [Rows]=' + RTRIM(@Rows)
            + ' then [金额] else 0 end)' + @s
           ,@Rows = @Rows - 1

EXEC('select [商品]'+@s+'  from (SELECT *,[Rows]=ROW_NUMBER()OVER(PARTITION BY 商品 ORDER BY 机构) FROM 表) as A group by [商品]')

[一只小小菜鸟]

解决了，谢谢各位啦！

[KeepSayingNo]

看看我这个，和你的需求一样 http://blog.csdn.net/dotnetstudio/article/details/22054499