sql 表列转行（交叉表）

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

    一、问题描述

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

       

          注:其中appraiserID是appraiser表的外键

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

        

    二、处理代码如下所示：       

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