sql server 2008 pivot table

Question

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

Answer 1

You have a few things wrong with your existing query.

First, I'm not sure why in your subquery you are using a GROUP BY on all of the columns. This is not necessary unless you know you have duplicates that you don't want in the final result.

Second, you are trying to display the [sum] column in the final select list but you are also aggregating this data in the PIVOT -- Sum(IA.[Sum]) -- you can't aggregate the data and display this value unless your subquery has this column listed twice.

Third, the aggregation used in the PIVOT is referencing the IA table alias - this alias is not available outside of the subquery so that syntax will not work either.

I would alter your query to use the following:

select [group], [number], [sum], DET, PLY, WTH
from
(
  select [group], [number], 
    [sum], 
    [sum] pivsum, SOURCE
  from dbo.Inter_App
) d
pivot
(
  sum(pivsum)
  for SOURCE in (DET, PLY, WTH)
) piv;

See SQL Fiddle with Demo. You'll notice that the subquery has two columns with the [sum] - one of these I gave an alias pivsum - this will be used for the aggregation in the PIVOT, the other column will be used in the final select list.

Finally, you stated that you might have an unknown number of Source values, if that is the case then you will need to use dynamic SQL to generate the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SOURCE) 
                    from Inter_App
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [group], [number], 
                [sum], ' + @cols + ' 
            from 
            (
              select [group], [number], 
                [sum], 
                [sum] pivsum, SOURCE
              from dbo.Inter_App
            ) x
            pivot 
            (
                sum(pivsum)
                for SOURCE in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. Both versions give a result:

| GROUP | NUMBER |   SUM |    DET |    PLY |    WTH |
|-------|--------|-------|--------|--------|--------|
|     a |      1 | -2503 | (null) | (null) |  -2503 |
|     a |      2 |  -180 |   -180 | (null) | (null) |
|     a |      3 |  -156 | (null) |   -156 | (null) |
|     a |      4 |   -99 |    -99 | (null) | (null) |
|     a |      5 |  -252 |   -252 | (null) | (null) |

Answer 2

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