multiple case SQL query retrieve single row as multiple column

Question

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.

Where does the data in column Type come from? There is nothing like it in the inputs. Or is it 1 for VT_Type1, etc.? In that case you have a typo in the desired output, the last row should have Type = 3; you show it as 2. — user5683823
– user5683823, Commented Apr 26, 2017 at 20:52
Also: you said when type "has" (is?) -1 then you want to retrieve a row, but if it "has" 0 then not to be considered. What about other values? In your example you have type = 1 and it is considered in the output. So - consider in the output, except when the type is 0? Or if not, what is the complete rule? Can the type also be null - and if so, should it be "considered" or ignored? — user5683823
– user5683823, Commented Apr 26, 2017 at 20:54
@mathguy VT_Type field possible values are -1 and 0. Should be considered only when it has -1 — Pat
– Pat, Commented Apr 26, 2017 at 21:05
Then please edit your post to correct it; you have the value 1 for id=2 in the vt_type3 column. — user5683823
– user5683823, Commented Apr 26, 2017 at 21:08

score 1 · Accepted Answer · 2017-04-26 21:19:34Z

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

edited Apr 26, 2017 at 21:19

answered Apr 26, 2017 at 21:14

user5683823

Sign up to request clarification or add additional context in comments.

2 Comments

Pat Over a year ago

in my actual table, if date_1 is of varchar data type and date_2 is of date data type, how to modify your answer. Actually in my table i have around 15 columns of this kind and i have given only 3 column here for example.

user5683823 Over a year ago

How were you planning to solve this issue regardless of which solution you were going to use? The same issue would arise in any solution. You probably should change all the values to dates; to convert a string to a date, you need to use to_date() with the appropriate format model. If you need more help on this aspect, then it would be best to open a separate question, linking back to this one if you feel necessary. In the example provided there, you would only need two columns (instead of three), but make sure the sample data you post reflects exactly the issue you raised.

Thorsten Kettner · Accepted Answer · 2017-04-26 21:00:02Z

1

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

answered Apr 26, 2017 at 21:00

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

multiple case SQL query retrieve single row as multiple column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related