unpivot table different datatypes with cases

Question

With the sql below I get the error my datatypes are not equal. C1 is varchar and C2 is a number. I found out pivot tables must be of the same datatype, but how would I convert the number into a varachar while using case statements such as below?

SELECT userID,

CASE columnname
WHEN 'c1' THEN
'Column1'
WHEN 'c2' THEN
'Column2'

END AS
columnname,

CASE columnname
WHEN 'c1' THEN
'1'
WHEN 'c2' THEN
'2'
END AS
"Extra info",
columnresult
FROM mytable unpivot( columnresult FOR columnname IN(c1,c2)) u

score 9 · Accepted Answer · answered May 06 '13 at 19:22

If the datatypes are different, then you need to cast them to be the same type before the UNPIVOT. The code will be similar to this:

SELECT userID,
    CASE columnname
        WHEN 'c1' THEN 'Column1'
        WHEN 'c2' THEN 'Column2'
    END AS columnname,
    CASE columnname
        WHEN 'c1' THEN '1'
        WHEN 'c2' THEN '2'
    END AS "Extra info",
    columnresult
FROM
(
    select userid, c1, cast(c2 as varchar2(10)) c2
    from mytable 
) 
unpivot
(
    columnresult 
    FOR columnname IN(c1,c2)
) u;

The difference is you now have a subquery that is used to select and cast the c1 and c2 columns to the same datatype before you unpivot the data into the same column.

unpivot table different datatypes with cases

1 Answers1