move multiple columns to rows and one row as column

Question

I have the result of my query in a temp table which looks something like shown below :

CREATE TABLE #temptable
(
productid INT,
date  DATE,
Indicator varchar(max),
VendorCode INT,
 morning INT,
 noon INT,
 evening INT
)



insert into #temptable values (101,'8-5-2016', 'High', 202, 0,1,0)
insert into #temptable values (101,'8-6-2016', 'High', 202, 0,0,1)
insert into #temptable values (101,'8-5-2016', 'Low', 202, 0,0,1)
insert into #temptable values (101,'8-6-2016', 'Low', 202, 0,0,1)
insert into #temptable values (101,'8-5-2016', 'Avg', 202, 1,0,1)
insert into #temptable values (101,'8-6-2016', 'Avg', 202, 0,0,1)



select * from #temptable

I need the output to look something like this :

I looked at using pivots but looks like that works only with aggregates ? Is there an easy way to do this ?

What's the logic behind the values of Low, High, Avg in the output — jonju, Aug 05 '16 at 19:08
what ever is in the table .. same is mapped to the output. no logic — CodeNinja, Aug 05 '16 at 19:14
Possible duplicate of [SQL transpose full table](http://stackoverflow.com/questions/15297809/sql-transpose-full-table) — techspider, Aug 05 '16 at 19:15

score 1 · Accepted Answer · answered Aug 05 '16 at 19:15

You can get the result you want by first applying the unpivot operator, and then pivot the result:

select 
    productid, VendorCode, date, time, Low, High, Avg
from (
    select productid, VendorCode, date, time, Indicator, val
    from #temptable 
    unpivot (val for time in ([morning],[noon],[evening])) u 
) t
pivot (max(val) for indicator in ([Low],[High],[Avg])) p
order by 
    productid, VendorCode, date, 
    case time
     when 'Morning' then 1 
     when 'Noon' then 2 
     when 'Evening' then 3 
    end

The case expression at the end of the order by clause makes sure the result is ordered correctly (Morning, Noon, Evening).

score 0 · Answer 2 · answered Aug 05 '16 at 19:14

First do UNPIVOT then PIVOT

SELECT
    piv.productid ,
    piv.date ,
    piv.VendorCode ,
    piv.Tmp AS [Time],
    piv.Low ,
    piv.High ,
    piv.Avg
from 
(  
    select *
    from #temptable
    unpivot
    (
      [Time]
      for [Tmp] in (morning, noon, evening)
    ) u
) src
pivot
(
  min([Time])
  for Indicator in ([Low], [High], [Avg])
) piv

Result:

productid   date        VendorCode  Time        Low     High    Avg
101         2016-08-05  202         evening     1       0       1
101         2016-08-05  202         morning     0       0       1
101         2016-08-05  202         noon        0       1       0
101         2016-08-06  202         evening     1       1       1
101         2016-08-06  202         morning     0       0       0
101         2016-08-06  202         noon        0       0       0

move multiple columns to rows and one row as column

2 Answers2