CODEWITHSUNDEEP
sqloracle-database
Pat
Pat

Reputation: 633

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       -1       -1 A        B        C        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    C     07/10

If type has -1 then corresponding status and date column should be retrieved as a row. As seen in above example ID 1 type1 has -1 and type2 has -1, so those 2 has to be converted in to 2 rows, type 3 has 0, so not to be considered. When i see examples online, i see case to be used but not sure how to use to service my need.

Upvotes: 0

Views: 178

Answers (2)

user5683823
user5683823

Reputation:

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

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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

Upvotes: 1

Related Questions