Oracle unpivot with multiple data types

Question

ID VT_Type1 Vt_type2 VT_Type3 Status_1 Status_2 Status_3 Date_1 Date_2 Date_3
 1       -1       -1        0 X        Y        2        04/12  05/12  06/12
 2       -1       -1       -1 A        B        1        06/12  07/12  07/10

Expected output

Id Type Status Date
1   1    X     04/12
1   2    Y     05/12
2   1    A     06/12
2   2    B     07/12
2   3    1     07/10

I could get the expected result by referring the answer in this multiple case SQL query retrieve single row as multiple column but in my table i referring columns have different data types. Like if VT_Type3 is -1 then i should read data from Status_3 which is of number where as other 2 columns is of varchar2.

Answer 1

Use a sub-query to, first, convert the Status_3 column to the same type as the others:

WITH test_data (
  ID,
  VT_Type1, Vt_type2, VT_Type3,
  Status_1, Status_2, Status_3, 
  Date_1, Date_2, Date_3
) AS (
       SELECT 1, -1, -1,  0, 'X', 'Y', 2, '04/12', '05/12', '06/12' FROM DUAL UNION ALL
       SELECT 2, -1, -1, -1, 'A', 'B', 1, '06/12', '07/12', '07/10' FROM DUAL
     )
SELECT  id, type, status, dt AS "DATE"
FROM    ( SELECT ID,
                 VT_Type1,
                 Vt_type2,
                 VT_Type3,
                 Status_1,
                 Status_2,
                 TO_CHAR( Status_3 ) AS status_3,
                 Date_1,
                 Date_2,
                 Date_3
          FROM   test_data
UNPIVOT ( (vt_type, status, dt)
          FOR type IN (
            ( vt_type1, status_1, date_1 ) as 1,
            ( vt_type2, status_2, date_2 ) as 2,
            ( vt_type3, status_3, date_3 ) as 3
          )
        )
WHERE   vt_type != 0;