SQL的行列转换问题，急，请大家帮忙！！

[szliukai]

现有的数据：
matcode corpcode price rate
abcd hd1 1 0.17
abcd hd2 2 0.17
abcd hd3 3 0.17
abn hd7 2 0.17
abn hd3 5 0.17
要转换成以下结果：
matcode corpcode1 price1 rate1 corpcode2 price2 rate2 corpcode3 price3 rate3 corpcode4 price4 rate4
abcd hd1 1 0.17 hd2 2 0.17 hd3 3 0.17 null null null
abn hd7 2 0.17 hd3 5 0.17 null null null null null null

请大家帮忙！！谢谢

[如一宝宝]

参考
http://www.cnblogs.com/fengxiang/archive/2009/08/15/1543833.html

[叶子]

表的主键是哪个？是复合主键？

[sinpoal]

http://blog.csdn.net/sinpoal/archive/2009/11/08/4785441.aspx
很多类似的……

[xlh0053]

我这里有个相似的，字段名称自己该一下
select 姓名 ,
max(case 课程 when '语文' then 分数 end ) 语文,
max(case 课程 when '数学' then 分数 end) 数学,
max(case 课程 when '物理' then 分数 end) 物理
from stufenshu
group by 姓名

[xiaoxiao8372]

INSERT INTO #sbcd
 SELECT  'abcd', 'hd1',1, 0.17 UNION ALL
 SELECT  'abcd' ,'hd2',2, 0.17 UNION ALL
 SELECT 'abcd', 'hd3',3, 0.17 UNION ALL
 SELECT 'abn' ,'hd7', 2,0.17 UNION ALL
 SELECT 'abn' ,'hd3', 5,0.17
 
SELECT * FROM #sbcd

SELECT matecode,
       MAX((CASE WHEN corpcode = 'hd1' THEN corpcode END)),
       MAX((CASE WHEN corpcode = 'hd1' THEN price END)),
       MAX((CASE WHEN corpcode = 'hd1' THEN rate END)),
       MAX((CASE WHEN corpcode = 'hd2' THEN corpcode END)),
       MAX((CASE WHEN corpcode = 'hd2' THEN price END)),
       MAX((CASE WHEN corpcode = 'hd2' THEN rate END))
FROM   #sbcd
GROUP BY  matecode

DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT matecode '
SELECT @sql=@sql + ',max(case when corpcode= '''+corpcode+''' then corpcode  end)
					,max(case when corpcode= '''+corpcode+''' then price  end)
					,max(case when corpcode= '''+corpcode+''' then rate  end)
					'
 FROM  (SELECT distinct corpcode from #sbcd) a
 SET @sql = @sql + ' from #sbcd group by matecode'
EXECUTE(@sql)


--静态
SELECT matecode ,max(case when corpcode= 'hd1' then corpcode  end) corpcode1
					,max(case when corpcode= 'hd1' then price  end) price1 
					,max(case when corpcode= 'hd1' then rate  end) rate1
					,max(case when corpcode= 'hd2' then corpcode  end) corpcode2
					,max(case when corpcode= 'hd2' then price  end) price2
					,max(case when corpcode= 'hd2' then rate  end) rate2
					,max(case when corpcode= 'hd3' then corpcode  end) corpcode3
					,max(case when corpcode= 'hd3' then price  end) price3
					,max(case when corpcode= 'hd3' then rate  end) rate3
						,max(case when corpcode= 'hd4' then corpcode  end)
					,max(case when corpcode= 'hd4' then price  end)
					,max(case when corpcode= 'hd4' then rate  end)
					,max(case when corpcode= 'hd7' then corpcode  end)
					,max(case when corpcode= 'hd7' then price  end)
					,max(case when corpcode= 'hd7' then rate  end)
					 from #sbcd group by matecode