表#t1数据如下：
id custcode summary amount amountin amountout balance
1 001 摘要01 400 20 30 390
2 001 摘要02 500 10 50 460
3 002 摘要01 600 40 640
4 003 摘要02 700 40 660
5 003 摘要01 800 50 850
6 004 摘要03 900 900
注明：balance=amount + amountin - amountout
我想把#t1的数据变成以下的数据显示：
cuscode 摘要01 摘要02 摘要03 amountin amountout balance
001 400 500 30 80 850
002 600 40 640
003 800 700 50 40 1510
004 900 900
注明：摘要01、摘要02、摘要03取的是amount的数据。
custcode=001的amountin是“摘要01”和“摘要02”的amountin的合计，balance=400+500+30-80=850。其它以此类推。
如何写sql2005的代码？
------解决思路----------------------
WITH a as(--測試數據
select 1 as id, '001' as custcode, '摘要01' as summary, 400 as amount, 20 as amountin, 30 as amountout, 390 as balance union all
select 2 as id, '001' as custcode, '摘要02' as summary, 500 as amount, 10 as amountin, 50 as amountout, 460 as balance union all
select 3 as id, '002' as custcode, '摘要01' as summary, 600 as amount, 40 as amountin, 0 as amountout, 640 as balance union all
select 4 as id, '003' as custcode, '摘要02' as summary, 700 as amount, NULL as amountin, 40 as amountout, 660 as balance union all
select 5 as id, '003' as custcode, '摘要01' as summary, 800 as amount, 50 as amountin, 0 as amountout, 850 as balance union all
select 6 as id, '004' as custcode, '摘要03' as summary, 900 as amount, NULL as amountin, 0 as amountout, 900 as balance
),
d AS (--行轉列
SELECT * FROM
(SELECT * FROM a ) AS b
PIVOT ( min(amount) for summary in(摘要01,摘要02,摘要03)) AS pvt)
--匯總
SELECT custcode,
sum(isnull(amountin,0)) AS amountin ,
sum(isnull(amountout,0)) as amountout ,
sum(isnull(balance,0)) as balance ,
sum(isnull([摘要01],0)) AS [摘要01] ,
sum(isnull([摘要02],0)) AS [摘要02] ,
sum(isnull([摘要03],0)) AS [摘要03]
FROM d d GROUP BY custcode
/*結果
custcode amountin amountout balance 摘要01 摘要02 摘要03
-------- ----------- ----------- ----------- ----------- ----------- -----------
001 30 80 850 400 500 0
002 40 0 640 600 0 0
003 50 40 1510 800 700 0
004 0 0 900 0 0 900
(4 個資料列受到影響)
*/