首页 新闻 会员 周边 捐助

求 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

damingming的主页 damingming | 初学一级 | 园豆:182
提问于:2012-02-16 17:14
< >
分享
最佳答案
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
小小刀 | 小虾三级 |园豆:1991 | 2012-02-17 22:19

虽然很麻烦 但是现在基本上是按这种方法写了.  但是不是直接写的,  为了以后修改起来方便, 把每个union的部分写成个存储过程. 分别对sbj 调用了.

damingming | 园豆:182 (初学一级) | 2012-02-18 15:20

@damingming: 可以采用拼sql的方式写啊!

小小刀 | 园豆:1991 (小虾三级) | 2012-02-18 15:36
其他回答(2)
0
收获园豆:5
dudu | 园豆:31033 (高人七级) | 2012-02-16 17:28

这个 我也有,但是好像不太行呢

支持(0) 反对(0) damingming | 园豆:182 (初学一级) | 2012-02-17 09:55
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
写代码的小2B | 园豆:4377 (老鸟四级) | 2012-02-17 14:14
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册