How to use SQL table pivot for a table with multiple aggregates

Question

I have a procedure that returns the following table:

enter image description here

And I want to pivot it around so that for each Name that is returned, you have a row for Planned, Actual and Difference.

For example:

|    Key   | Name1  |  Name2  |  Name3  |  Name4
| Planned  |   0    |    0    |    0    |    0 
|  Actual  |  8957  |   5401  |   NULL  |   NULL
|Difference| -8957  |  -5401  |   NULL  |   NULL

I'm trying to use the PIVOT function, but I've never used it before and am struggling to get my head around it. How would one achieve something similar to the above?

If you do not explicitly know the names, then you need a dynamic pivot. Just google "sql server dynamic pivot" and you can get examples. Otherwise, refer to the Microsoft documentation on pivot. — Gordon Linoff
– Gordon Linoff, Commented Jun 20, 2014 at 10:10
You'll probably need to unpivot first to generate 12 rows with Name and Status columns and all 12 values in distinct rows, and then pivot based off of that. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 20, 2014 at 10:18

PaMcD · Accepted Answer · 2014-06-20 15:54:51Z

Without a pivot, you can use a cross join instead

Note this only works if you know how many names you will have everytime you run it and if each name only appears once in the original table.(otherwise the max function below is not appropriate)

create table #test(ID int, Name char(5), planned int, actual int, difference_between int)
insert into dbo.#test
values
(54, 'Name1', 0, 8975, -8957),
(54, 'Name2', 0, 5401, -5401),
(54, 'Name3', 0, NULL, NULL),
(54, 'Name4', 0, NULL, NULL)


select case t.occurno when 1 then 'Planned' when 2 then 'Actual' when 3 then 'Difference' end as [Key]
  , max(case when Name = 'name1' then case t.occurno when 1 then planned when 2 then actual when 3 then difference_between else 0 end end) as Name1
  , max(case when Name = 'name2' then case t.occurno when 1 then planned when 2 then actual when 3 then difference_between else 0 end end) as name2
  , max(case when Name = 'name3' then case t.occurno when 1 then planned when 2 then actual when 3 then difference_between else 0 end end) as name3
  , max(case when Name = 'name4' then case t.occurno when 1 then planned when 2 then actual when 3 then difference_between else 0 end end) as name4


from dbo.#test

cross join
(select top 3 ROW_NUMBER() over(order by occurno) as occurno
    from (select 1 as occurno) t
    group by cube(occurno,occurno,occurno,occurno)
) t

group by t.occurno

ventik · Accepted Answer · 2014-06-20 12:47:03Z

create table #T
(
    Name varchar(255),
    Planned int,
    Actual int,
    [Difference] int
)

insert into #T(Name, Planned, Actual, [Difference])
select 'Name1', 0, 8957, -8957 
union
select 'Name2', 0, 5401, -5401
union
select 'Name3', 0, NULL, NULL
union
select 'Name4', 0, NULL, NULL

-- unpivoting data
create table #T2
(
    [Key] varchar(255),
    Name varchar(255),
    Value int
)

insert into #T2
select [Key], Name, Value
from 
   (select Name, Planned, Actual, [Difference] from #T) P
UNPIVOT
   (Value for [Key] IN (Planned, Actual, [Difference])) as UNP

-- getting sequence of column names   
declare @columns nvarchar(max)
set @columns = ''
select @columns = @columns + ', [' + Name + ']'
from (select distinct Name from #T2) as T
order by Name

set @columns = substring(@columns, 2, len(@columns))

declare @sql nvarchar(max)

-- building dynamic sql for pivoting
set @sql = 
'
SELECT *
FROM
(SELECT 
        [Key], Name, Value 
    FROM 
        #T2
) AS SourceTable
PIVOT
(
    SUM(Value)
    FOR Name in ('+@columns+')
) AS PivotTable
order by 
    case [Key] 
        when ''Planned'' then 1
        when ''Actual'' then 2
        when ''Difference'' then 3 end
'

exec sp_executeSQL @SQL 

drop table #T2
drop table #T

Collectives™ on Stack Overflow

How to use SQL table pivot for a table with multiple aggregates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related