Unpivot multiple columns and value

Question

I have a query with period and three columns, which are of different data types.

abc: int
def: decimal
hij: int

Data for these three columns provides come from different queries.

Here is the current result:

Period    abc   def   hij
------    ----  ----  ---
2012-01    10   0.00    4
2012-02     5   0.00    5
2012-03     0   2.40    8

I would like to get the following output:

abc 2012-01 10 0.00
abc 2012-02  5 0.00
abc 2012-03  0 0.00
def 2012-01  0 0.00
def 2012-02  0 0.00
def 2012-03  0 2.40
hij 2012-01  4 0.00
hij 2012-02  5 0.00
hij 2012-03  8 0.00

I would like to use unpivot but it gives me an error of conflict type.

Could you provide the queries used to produce the current result, to make it easier to answer your question? — erikxiv, Apr 23 '12 at 19:17
Look at this [answer](http://stackoverflow.com/questions/10266494/how-to-change-the-information-in-this-table-into-an-easy-to-use-form/10266894#10266894). — Bogdan Sahlean, Apr 23 '12 at 21:45

John Dewey · Answer 1 · 2012-04-23T20:19:44.593

The error you are getting is because you are mixing decimal with int in the UNPIVOT.

It looks like you are wanting to UNPIVOT but then separate def back out from the other columns again.

I was able to achieve what appears to be the desired result like this:

declare @t table(Period varchar(10), abc int, def decimal(5,2), hij int)
insert into @t values ('2012-01',10,0.00,4)
, ('2012-02',5,0.00,5)
, ('2012-03',0,2.40,8);

with a as (
    select cols, Period, val
    from (select Period, abc=CAST(abc as decimal(5,2)), def, hij=CAST(hij as decimal(5,2)) from @t) p
    UNPIVOT (
        val for cols in (abc, def, hij) 
    ) as unpvt
)
select a.cols
, Period
, abc_hij=case when cols in ('abc','hij') then CAST(val as int) else 0 end
, def=case when cols = ('def') then val else 0.00 end
from a
order by cols, Period
go

Result:

enter image description here

You are welcome. If this works for you, please check the Accepted checkbox by the solution:) — John Dewey, Apr 24 '12 at 14:12

score 0 · Answer 2 · answered Nov 02 '15 at 09:19

WITH c AS (
SELECT  '2012-01' AS Period,    10 AS abc ,  0.00 AS def,    4 AS hij
UNION ALL
SELECT  '2012-02' AS Period,     5 AS abc,   0.00 AS def ,   5 AS hij
UNION ALL
SELECT  '2012-03' AS Period,     0  AS abc,  2.40 AS def,    8 AS hij
)
SELECT * FROM (
SELECT cc.title, c.Period,cc.val FROM c
cross apply
(
  VALUES('abc',abc),('def',def),('hij',hij)
) cc (title,val)
) t
ORDER BY t.title

Unpivot multiple columns and value

2 Answers2