sql server 2008 pivot table

Rueful Rabbit

I have the following table ...

GROUP   Number  Sum     SOURCE
a           1   -2503   WTH
a           2   -180    DET
a           3   -156    PLY
a           4   -99     DET
a           5   -252    DET

Which I'd like to present as follows ...

GROUP   Number  Sum      DET    PLY     WTH
a           1   -2503                   -2503
a           2   -180    -180        
a           3   -156           -156 
a           4   -99     -99     
a           5   -252    -252

Here is what I've tried (unsuccessfully) using PIVOT ...

SELECT
    [GROUP]
    ,Number
    ,Opening_Val
    ,[Sum]
    ,DET
    ,PLY
    ,WTH
FROM
(SELECT 
     IA.GROUP_CD
    ,IA.Number
    ,IA.[sum]
    ,Src
FROM dbo.##Inter_App IA
GROUP BY IA.[GROUP]
    ,IA.Number
    ,IA.[sum]
    ,Src ) query
PIVOT 
(   Sum(IA.[Sum])
    For Src in (DET, PLY, WTH)
) pvt

Ideally, I'll like not to limit the columns to (DET, PLY, WTH) as there may be more SOURCE's that I'm not aware of.

Any help appreciated.

Thanks, James

AdamL

You made your attempt too complicated :). Also, pick other names for your columns instead of sum, group and number, because not only those are sql-syntax keywords, it also makes the queries harder to read (e.g. sum([sum]), group by [group]).

drop table #temp
GO
select 
    *
into #temp
from (
    select 'a' as [group],1 as [number],'-2503' as [sum],'WTH' as [source] union all
    select 'a',2,-180,'DET' union all
    select 'a',3,-156,'PLY' union all
    select 'a',4,-99,'DET' union all
    select 'a',5,-252,'DET' 
) x
GO   

select 
    [group], [number], 
    det, ply, wth
from #temp
pivot (
    sum([sum]) for [source] in (det,ply,wth)
) x

Collected from the Internet

Please contact [email protected] to delete if infringement.