Oracle comma seperated to rows with grouping

Question

I have the following requirement. Do we have direct functions available in oracle 12c to accomplish this.

create table t1(input_name varchar2(500),input_values varchar2(500));
insert into t1 values('a,b,c,d,','1,2,3,4');
insert into t1 values('e,f,g,','5,6,7');
insert into t1 values('a1,b1,c1,d1,','11,12,13,14');
insert into t1 values('d,c,b,a,','100,200,300,400');
commit;

select * from t1;
INPUT_NAME                     INPUT_VALUES
------------------------------ ----------------
a,b,c,d,                       1,2,3,4
e,f,g,                         5,6,7
a1,b1,c1,d1,                   11,12,13,14
d,c,b,a,                       100,200,300,400

output:
a   b   c   d   e f g a1 b1 c1 d1
1   2   3   4   5 6 7 11 12 13 14
400 300 200 100

Thanks, Rahmat Ali

Do you expect your output to be a concatenation of the strings or do you want a pivot with each input name being in a separate column? — MT0, Jan 30 '18 at 12:44
Thanks for the quick response, Yes I need pivot for each input name. — Rahmat, Jan 30 '18 at 13:58
Thanks for the quick response, yes indeed, I am looking for pivot for each input name, Is there a way where I can avoid using types? — Rahmat, Jan 30 '18 at 13:59
Thanks MTO, the solution was working but had perf issues, so we opted for Java transformation script in informatica. — Rahmat, Feb 01 '18 at 08:04

MT0 · Answer 1 · 2018-01-30T14:56:49.483

Yes... if you have a known set of input names. But you would be better reorganising your data so that you are not storing correlated pairs of comma-separated lists.

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table t1(input_name,input_values) AS
SELECT 'a,b,c,d,','1,2,3,4' FROM DUAL UNION ALL
SELECT 'e,f,g,','5,6,7' FROM DUAL UNION ALL
SELECT 'a1,b1,c1,d1,','11,12,13,14' FROM DUAL UNION ALL
SELECT 'd,c,b,a,','100,200,300,400' FROM DUAL
/
CREATE TYPE pair IS OBJECT(
  name  VARCHAR2(20),
  value VARCHAR2(20)
)
/

CREATE TYPE pair_table IS TABLE OF PAIR
/

Query 1:

SELECT MAX( CASE name WHEN 'a' THEN value END ) AS a,
       MAX( CASE name WHEN 'b' THEN value END ) AS b,
       MAX( CASE name WHEN 'c' THEN value END ) AS c,
       MAX( CASE name WHEN 'd' THEN value END ) AS d,
       MAX( CASE name WHEN 'e' THEN value END ) AS e,
       MAX( CASE name WHEN 'f' THEN value END ) AS f,
       MAX( CASE name WHEN 'g' THEN value END ) AS g,
       MAX( CASE name WHEN 'a1' THEN value END ) AS a1,
       MAX( CASE name WHEN 'b1' THEN value END ) AS b1,
       MAX( CASE name WHEN 'c1' THEN value END ) AS c1,
       MAX( CASE name WHEN 'd1' THEN value END ) AS d1
FROM   (
  SELECT v.name,
         v.value,
         ROW_NUMBER() OVER ( PARTITION BY v.name ORDER BY ROWNUM ) AS rn
  FROM   t1 t
         CROSS JOIN
         TABLE(
           CAST(
             MULTISET(
               SELECT pair(
                        REGEXP_SUBSTR( t.input_name,   '([^,]+)(,|$)', 1, LEVEL, NULL, 1 ),
                        REGEXP_SUBSTR( t.input_values, '([^,]+)(,|$)', 1, LEVEL, NULL, 1 )
                      )
               FROM   DUAL
               CONNECT BY level <= REGEXP_COUNT( t.input_name, '([^,]+)(,|$)' )
             ) AS pair_table
           )
         ) v
)
GROUP BY rn

Results:

|   A |   B |   C |   D |      E |      F |      G |     A1 |     B1 |     C1 |     D1 |
|-----|-----|-----|-----|--------|--------|--------|--------|--------|--------|--------|
|   1 |   2 |   3 |   4 |      5 |      6 |      7 |     11 |     12 |     13 |     14 |
| 400 | 300 | 200 | 100 | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

You can also use a PIVOT statement rather than multiple MAX( CASE ... END ) statements.

If you do not have a static set of input names then you will need to search for dynamic pivots.

Update:

Is there a way where I can avoid using types?

You can avoid creating types and just use a built-in VARRAY or collection like SYS.ODCIVARCHAR2LIST but then you will need two lists and it becomes complicated to correlate between the two.

WITH input_names ( rid, idx, name ) AS (
  SELECT t.ROWID,
         ROW_NUMBER() OVER ( PARTITION BY t.ROWID ORDER BY ROWNUM ) AS rn,
         v.COLUMN_VALUE
  FROM   t1 t
         CROSS JOIN
         TABLE(
           CAST(
             MULTISET(
               SELECT REGEXP_SUBSTR( t.input_name,   '([^,]+)(,|$)', 1, LEVEL, NULL, 1 )
               FROM   DUAL
               CONNECT BY level <= REGEXP_COUNT( t.input_name, '([^,]+)(,|$)' )
             ) AS SYS.ODCIVARCHAR2LIST
           )
         ) v
),
input_values ( rid, idx, value ) AS (
  SELECT t.ROWID,
         ROW_NUMBER() OVER ( PARTITION BY t.ROWID ORDER BY ROWNUM ) AS rn,
         v.COLUMN_VALUE
  FROM   t1 t
         CROSS JOIN
         TABLE(
           CAST(
             MULTISET(
               SELECT REGEXP_SUBSTR( t.input_values,   '([^,]+)(,|$)', 1, LEVEL, NULL, 1 )
               FROM   DUAL
               CONNECT BY level <= REGEXP_COUNT( t.input_values, '([^,]+)(,|$)' )
             ) AS SYS.ODCIVARCHAR2LIST
           )
         ) v
),
correlated ( name, value, rn ) AS (
  SELECT n.name,
         v.value,
         ROW_NUMBER() OVER ( PARTITION BY n.name
                             ORDER BY ROWNUM )
  FROM   input_names n
         INNER JOIN
         input_values v
         ON ( n.rid = v.rid AND n.idx = v.idx )
)
SELECT MAX( CASE name WHEN 'a' THEN value END ) AS a,
       MAX( CASE name WHEN 'b' THEN value END ) AS b,
       MAX( CASE name WHEN 'c' THEN value END ) AS c,
       MAX( CASE name WHEN 'd' THEN value END ) AS d,
       MAX( CASE name WHEN 'e' THEN value END ) AS e,
       MAX( CASE name WHEN 'f' THEN value END ) AS f,
       MAX( CASE name WHEN 'g' THEN value END ) AS g,
       MAX( CASE name WHEN 'a1' THEN value END ) AS a1,
       MAX( CASE name WHEN 'b1' THEN value END ) AS b1,
       MAX( CASE name WHEN 'c1' THEN value END ) AS c1,
       MAX( CASE name WHEN 'd1' THEN value END ) AS d1
FROM   correlated
GROUP BY rn;

with kv_pair as (select distinct REGEXP_SUBSTR(input_name, '([^,]+)(,|$)', 1, LEVEL, NULL, 1 ) input_name, REGEXP_SUBSTR(input_values, '([^,]+)(,|$)', 1, LEVEL, NULL, 1 ) input_value from t1 CONNECT BY level <= REGEXP_COUNT( input_name, '([^,]+)(,|$)' ) ) — Rahmat, Jan 30 '18 at 14:36
Can I build on top this query INPUT_NAME INPUT_VALU ---------- ---------- a 400 a 1 a1 11 b 2 b 300 b1 12 c 3 c 200 c1 13 d 100 d 4 d1 14 e 5 f 6 g 7 — Rahmat, Jan 30 '18 at 14:37

Oracle comma seperated to rows with grouping

1 Answers1