CODEWITHSUNDEEP
oracle-databaseplsql
Victor
Victor

Reputation: 23

Generate rows based on multiple column

I want to return multiple rows with same data based on code columns data. I can achieve this with UNION ALL.Is there any way to do this without UNION ALL. I'm having more than 10 columns (S1, S2,....S10). In sample data i have provided only 3 columns (S1, S2, S3).

SELECT ID , NAME , S1 SCODE 
  FROM TBL1
  WHERE S1 IS NOT NULL
UNION ALL
SELECT ID , NAME , S2 SCODE 
  FROM TBL1
  WHERE S2 IS NOT NULL

Actual Table:

ID NAME S1 S2 S3
1  T1   AA BB CC
2  T2   AA    KK

Expected Result:

ID  NAME SCODE
1   T1   AA
1   T1   BB
1   T1   CC
2   T2   AA
2   T2   KK

Upvotes: 2

Views: 295

Answers (2)

Andrei Odegov
Andrei Odegov

Reputation: 3439

Try the UNPIVOT clause:

with
  test (id, name, s1, s2, s3) as (
    select 1, 'T1', 'AA', 'BB', 'CC' from dual union all
    select 2, 'T2', 'AA', null, 'KK' from dual
  )
select id, name, scode
from test
unpivot (scode for cols in (S1, S2, S3)) up;

Output:

| ID | NAME | SCODE |
+----+------+-------+
|  1 | T1   | AA    |
|  1 | T1   | BB    |
|  1 | T1   | CC    |
|  2 | T2   | AA    |
|  2 | T2   | KK    |

Test it online with rextester.com.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143003

Here's one option: concatenate Sx columns, and then do the reverse - split them to rows.

SQL> with test (id, name, s1, s2, s3) as
  2    (select 1, 'T1', 'AA', 'BB', 'CC' from dual union all
  3     select 2, 'T2', 'AA', null, 'KK' from dual
  4    ),
  5  temp as
  6    (select id,
  7            name,
  8            s1 || decode(s1, null, null, ':') ||
  9            s2 || decode(s2, null, null, ':') ||
 10            s3 col
 11     from test
 12    )
 13  select id,
 14         name,
 15         regexp_substr(col, '[^:]+', 1, column_value) scode
 16  from temp join table(cast(multiset(select level from dual
 17                                     connect by level<= regexp_count(col, ':') + 1
 18                                    ) as sys.odcinumberlist)) on 1 = 1;

        ID NA SCODE
---------- -- --------------------------------
         1 T1 AA
         1 T1 BB
         1 T1 CC
         2 T2 AA
         2 T2 KK

SQL>

Upvotes: 1

Related Questions