Pivot table Q - SQL Server 2008

Rueful Rabbit

I have the following table ...

GROUPING    Num DEF  TOTAL   SOURCE
a           1   a    -2503   WTH
a           2   b    -180    DET
a           2   c    -156    PLY
a           4   d    -99     DET
a           5   e    -252    DET

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

GROUPING    Num    DET      PLY     WTH
a           1                       -2503
a           2      -180     -156
a           4      -99      
a           5      -252

Here is my effort ...

select 
    grouping
    ,num 
    ,det
    ,ply
    ,wth
from #temp
pivot (
    sum([TOTAL]) for [SOURCE] in (det,ply,wth)
) x

But I get 2 rows with num = 2 where I want 1 row.

Any help appreciated.

Thanks, James

Taryn

Your existing query is not using a subquery to select from your table, as a result all of the columns not used by the PIVOT are used in the GROUP BY. Since you have multiple distinct values for the DEF column you are returning multiple rows for num=2.

I would use a subquery to select only the columns you need for the PIVOT and the final select list:

select 
    grouping
    ,num 
    ,det
    ,ply
    ,wth
from
(
  select grouping, num, total, source
  from #temp
) d
pivot 
(
  sum([TOTAL]) 
  for [SOURCE] in (det,ply,wth)
) x;

See SQL Fiddle with Demo

Collected from the Internet

Please contact [email protected] to delete if infringement.