CODEWITHSUNDEEP
sqlt-sqlpivot
user1636130
user1636130

Reputation: 1683

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

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?

Upvotes: 0

Views: 96

Answers (2)

PaMcD
PaMcD

Reputation: 115

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

Upvotes: 1

ventik
ventik

Reputation: 887

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

Upvotes: 0

Related Questions