Convert column into rows with same data SQL

Question

I have a table named t1 which is having 6 column. Now i need to create a report based on columnwith each data in C,D,E Column as separate rows like table t2. How to write a SQL query for the same in oracle.

TABLE T1 (current table values)

A   B   C   D       E       F
1   AE  21  S-1     F-A     POST
1   AE  31  NULL    NULL    POST
1   AE  41  NULL    NULL    PRE
1   AE  51  S-2     R-A     PRE

TABLE T2 (Excepted result)

A   B   C       D       E       F
1   AE  21      NULL    NULL    POST
1   AE  NULL    S-1     NULL    POST
1   AE  NULL    NULL    F-A     POST
1   AE  31      NULL    NULL    POST
1   AE  41      NULL    NULL    PRE
1   AE  NULL    S-2     NULL    PRE
1   AE  NULL    NULL    R-A     PRE
1   AE  51      NULL    NULL    PRE

It's called tranpose. Pivot and crosstab are related terms too. You can find a lot of good info out there. — shawnt00
– shawnt00, Commented Aug 11, 2015 at 7:56

Barett · Accepted Answer · 2015-08-11 16:23:03Z

2

Use a UNION query to grab each field value separately. Include your key values (a,b), then each non-null field on its own. Here's an example:

select a,b, c, null d, null e, f from t1 where c is not null
union
select a,b, null c, d, null e, f from t1 where d is not null
union
select a,b, null c, null d, e, f from t1 where e is not null

edited Aug 11, 2015 at 16:23

Barett

5,9546 gold badges54 silver badges57 bronze badges

answered Aug 11, 2015 at 7:44

Viktor Bardakov

8766 silver badges16 bronze badges

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

Comments

kangbu · Accepted Answer · 2015-08-11 07:52:07Z

I made another table t0 and used it like this:

with t1 as 
        (select 1 A, 'AE' B, 21 C, 'S-1' D, 'F-A' E, 'POST' F from dual union all
         select 1,   'AE',   31,   null ,   null,    'POST' from dual union all
         select 1,   'AE',   41,   null ,   null,    'PRE' from dual union all
         select 1,   'AE',   51,   'S-1' ,  'R-A',   'PRE' from dual),
t0 as 
        (select 'C' x from dual union all
         select 'D' from dual union all
         select 'E' from dual)
select a,
       b,
       case when t0.x = 'C' then c end c,
       case when t0.x = 'D' then d end d,
       case when t0.x = 'E' then e end e,
       f
  from t1,t0 
 where not (    case when t0.x = 'C' then c end is null
            and case when t0.x = 'D' then d end is null
            and case when t0.x = 'E' then e end is null)

Collectives™ on Stack Overflow

Convert column into rows with same data SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related