SQL | SPLIT COLUMNS INTO ROWS-CodePudding

How can I split the column data into rows with basic SQL.

COL1 COL2
1     A-B
2     C-D
3     AAA-BB

Result

COL1 Col2
1     A
1     B
2     C
2     D
3     AAA
3     BB

CodePudding user response：

As of Oracle:

SQL> with test (col1, col2) as
  2    (select 1, 'A-B' from dual union all
  3     select 2, 'C-D' from dual union all
  4     select 3, 'AAA-BB' from dual
  5    )
  6  select col1,
  7    regexp_substr(col2, '[^-] ', 1, column_value) col2
  8  from test cross join
  9    table(cast(multiset(select level from dual
 10                        connect by level <= regexp_count(col2, '-')   1
 11                       ) as sys.odcinumberlist))
 12  order by col1, col2;

      COL1 COL2
---------- ------------------------
         1 A
         1 B
         2 C
         2 D
         3 AAA
         3 BB

6 rows selected.

SQL>

CodePudding user response：

For MS-SQL 2016 and higher you can use:

SELECT Col1, x.value
FROM t CROSS APPLY STRING_SPLIT(t.Col2, '-') as x;

BTW: If Col2 contains null, it does not appear in the result.

CodePudding user response：

Snowflake is tagged, so here's the snowflake way of doing this:

WITH TEST (col1, col2) as
      (select 1, 'A-B' from dual union all
       select 2, 'C-D' from dual union all
       select 3, 'AAA-BB' from dual
      )
SELECT test.col1, table1.value 
FROM test, LATERAL strtok_split_to_table(test.col2, '-') as table1
ORDER BY test.col1, table1.value;

CodePudding user response：

From Oracle 12, if it is always two delimited values then you can use:

SELECT t.col1,
       l.col2
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT SUBSTR(col2, 1, INSTR(col2, '-') - 1) AS col2 FROM DUAL
         UNION ALL
         SELECT SUBSTR(col2, INSTR(col2, '-')   1) FROM DUAL
       ) l

Which, for the sample data:

CREATE TABLE table_name (COL1, COL2) AS
SELECT 1, 'A-B' FROM DUAL UNION ALL
SELECT 2, 'C-D' FROM DUAL UNION ALL
SELECT 3, 'AAA-BB' FROM DUAL;

Outputs:

COL1 COL2

1 A

1 B

2 C

2 D

3 AAA

3 BB

COL1	COL2
1	A
1	B
2	C
2	D
3	AAA
3	BB

db<>fiddle here