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' THENAmount END) AS RentBalance,
SUM(CASE WHEN PaymentType = '09' THEN Amount END) AS RentWriteOff,
SUM(CASE WHEN PaymentType = '04' THENAmount END) AS MaintenanceWriteOff,
SUM(CASE WHEN PaymentType = '05' THEN Amount END) AS UnclaimedMoniesExternalRent,
SUM(CASE WHEN PaymentType = '06' THENAmount END) AS UnclaimedMoniesExternalMaintenance
FROM dbo.V_CM_CustCurBalByType v
GROUP BY AccountNum
最后得到的结果类似于下面这样：
大概就是这样了！另外我收集了几篇关于列转行和行转列的文章，感兴趣的可以去看看！
http://www.iyunv.com/kerrycode/archive/2010/07/28/1786547.html
http://www.iyunv.com/chinabc/archive/2009/12/15/1624913.html
页:
[1]