求 SQL语句 (SQL Server) 高级行列转换 求指点
0
悬赏园豆：20
[已解决问题]
解决于 2012-02-18 15:23
列名 AreaCd sbj1 sbj2 sbj3
1 a a a
2 b b b
3 c c c
4 d d d
转换成
列名 colID Area_1 Area_2 Area_3 Area_4
sbj1 a b c d
sbj2 a b c d
sbj3 a b c d
最佳答案
0
select 'sbj1' as colID,
max(case when AreaCd=1 then sbj1 else '' end) as Area_1,
max(case when AreaCd=2 then sbj1 else '' end) as Area_2,
max(case when AreaCd=3 then sbj1 else '' end) as Area_3,
max(case when AreaCd=4 then sbj1 else '' end) as Area_4
from TB
union
select 'sbj2' as colID,
max(case when AreaCd=1 then sbj2 else '' end) as Area_1,
max(case when AreaCd=2 then sbj2 else '' end) as Area_2,
max(case when AreaCd=3 then sbj2 else '' end) as Area_3,
max(case when AreaCd=4 then sbj2 else '' end) as Area_4
from TB
union
select 'sbj3' as colID,
max(case when AreaCd=1 then sbj3 else '' end) as Area_1,
max(case when AreaCd=2 then sbj3 else '' end) as Area_2,
max(case when AreaCd=3 then sbj3 else '' end) as Area_3,
max(case when AreaCd=4 then sbj3 else '' end) as Area_4
from TB
收获园豆：10
虽然很麻烦 但是现在基本上是按这种方法写了. 但是不是直接写的, 为了以后修改起来方便, 把每个union的部分写成个存储过程. 分别对sbj 调用了.
@damingming: 可以采用拼sql的方式写啊！
其他回答(2)
0
0
SELECT 'sbj1',* FROM (
SELECT AreaCd,sbj1 FROM #TEMP
) AS A Pivot (MAX([sbj1]) For AreaCd In ([1],[2],[3],[4])) AS B
UNION ALL
SELECT 'sbj2',* FROM (
SELECT AreaCd,sbj2 FROM #TEMP
) AS A Pivot (MAX([sbj2]) For AreaCd In ([1],[2],[3],[4])) AS B
UNION ALL
SELECT 'sbj3',* FROM (
SELECT AreaCd,sbj3 FROM #TEMP
) AS A Pivot (MAX([sbj3]) For AreaCd In ([1],[2],[3],[4])) AS B
抛砖引玉，坐等大牛。
收获园豆：5