Multiple Case SQL Query Retrieve Single Row As Multiple Column

Table: 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 Z 04/12 05/12 06/12 2 -1

Solution 1:

In Oracle 11.1 and above, you can use the UNPIVOT operator. If you have a large volume of data, this will provide a significant improvement in execution time, as it requires reading the table just one time, instead of three times with any kind of UNION ALL approach.

I changed the column name (in the output) from date to dt since DATE is a reserved word in Oracle. type isn't much better (it is a keyword but it is not reserved); best to avoid it too. I also treated your dates as strings when I created the test data, but it works just the same with dates.

The with clause is not part of the solution (don't blindly copy and paste it with the query); I added it just for testing purposes.

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', 'Z', '04/12', '05/12', '06/12' from dual union all
       select 2, -1, -1, -1, 'A', 'B', 'C', '06/12', '07/12', '07/10' from dual
     )
select  id, type, status, dt
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
;

ID  TYPE  STATUS  DT
--  ----  ------  -----
 1     1  X       04/12
 1     2  Y       05/12
 2     1  A       06/12
 2     2  B       07/12
 2     3  C       07/10

Solution 2:

Query the table thrice, once for each type:

select id, 1 as type, status_1 as status, date_1 as "date" from mytable where VT_Type1 = -1
union all
select id, 2 as type, status_2 as status, date_2 as "date" from mytable where VT_Type2 = -1
union all
select id, 3 as type, status_3 as status, date_3 as "date" from mytable where VT_Type3 = -1