sql 表列转行（交叉表）

     折腾我好几天的交叉表问题终于解决了。下面将代码贴出来。

    一、问题描述

      数据库中存在一个名为landundertake表，结构如下所示：

       

          注:其中appraiserID是appraiser表的外键

     目的是将landundertake表转换为如下所示： 

        

    二、处理代码如下所示：       

 1 select 
 2     B1.A as quarterID,
 3     B1.A1 as landCode,
 4     replicate('0',3-len(B1.A2))+cast(B1.A2 AS varchar(10)) as appraiserID1,--字符串是定长，如果长度不足，用0填充
 5     replicate('0',3-len(B1.A3))+cast(B1.A3 as varchar(10)) as appraiserID2 --字符串是定长，如果长度不足，用0填充
 6 from
 7     (
 8         select 
 9             Appraiser.quarterID as A,
10             landundertake.landCode as A1,
11             max(case landundertake.appraiserGrade when 'appraiserID1' then landundertake.appraiserID else 0 end) as A2,
12             max(case landundertake.appraiserGrade when 'appraiserID2' then landundertake.appraiserID else 0 end) as A3
13          from landundertake inner join
14             Appraiser on landundertake.quarterID=Appraiser.quarterID and landundertake.appraiserID=Appraiser.appraiserID
15           GROUP BY landundertake.landCode,Appraiser.quarterID
16     ) AS B1

       

     

转载于:https://www.cnblogs.com/Peter-Luo/archive/2012/02/13/sqljiaochabiao.html