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

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

i have procedure returns next table:

and want pivot around each name returned, have row planned, actual , 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 utilize pivot function, i've never used before , struggling head around it. how 1 accomplish similar above?

without pivot, can utilize cross bring together instead

note works if know how many names have everytime run , if each name appears 1 time in original table.(otherwise max function below not appropriate)

create table #test(id int, name char(5), planned int, actual int, difference_between int) insert 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 'planned' when 2 'actual' when 3 'difference' end [key] , max(case when name = 'name1' case t.occurno when 1 planned when 2 actual when 3 difference_between else 0 end end) name1 , max(case when name = 'name2' case t.occurno when 1 planned when 2 actual when 3 difference_between else 0 end end) name2 , max(case when name = 'name3' case t.occurno when 1 planned when 2 actual when 3 difference_between else 0 end end) name3 , max(case when name = 'name4' case t.occurno when 1 planned when 2 actual when 3 difference_between else 0 end end) name4 dbo.#test cross bring together (select top 3 row_number() over(order occurno) occurno (select 1 occurno) t grouping cube(occurno,occurno,occurno,occurno) ) t grouping t.occurno

sql tsql pivot