SQL SERVER 列转行

最近在写SP的时候遇到了将表中数据列转行的问题，在此记录一下，以备后用！有不对或不妥之处，还望大家能够指出！谢谢！

首先我用的是一个View，这个view大概是下面这样子的：

一个AccountNum有多种PaymentType，每种PaymentType对应一个Amount，现在要求根据accountnum求出所有的PaymentType的amount，刚开始我是join了这个View 6次（我只需要取其中六种的PaymentType的amount），后发现效率太低了，经过和同事讨论，才想出用此方法来解决！主要就是用case when来实现，下面是脚本：

 

            SELECT AccountNum,                    
                   SUM(CASE WHEN PaymentType = '01' THEN Amount END) AS MaintenanceBalance,
                   SUM(CASE WHEN PaymentType = '07' THEN  Amount END) AS RentBalance,
                   SUM(CASE WHEN PaymentType = '09' THEN Amount END) AS RentWriteOff,
                   SUM(CASE WHEN PaymentType = '04' THEN  Amount END) AS MaintenanceWriteOff,
                   SUM(CASE WHEN PaymentType = '05' THEN Amount END) AS UnclaimedMoniesExternalRent,
                   SUM(CASE WHEN PaymentType = '06' THEN  Amount END) AS UnclaimedMoniesExternalMaintenance

            FROM dbo.V_CM_CustCurBalByType v

            GROUP BY AccountNum 

 

最后得到的结果类似于下面这样：

大概就是这样了！另外我收集了几篇关于列转行和行转列的文章，感兴趣的可以去看看！

http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

http://www.cnblogs.com/chinabc/archive/2009/12/15/1624913.html