SQL | SPLIT COLUMNS INTO ROWS

Question

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

Are you using MySQL or Oracle? (The answer will not suit both...) — jarlh, Dec 10 '21 at 19:21
Will it always be the first and third character? (Or can it be ABC-DD?) — jarlh, Dec 10 '21 at 19:22
Or is it simply delineated by a `-` to denote splits and you could have A-B-C-D and need 4 rows? — xQbert, Dec 10 '21 at 19:24
If ABC-DD or A-B-C-D are possible, add those to the sample data and also adjust the expected result. — jarlh, Dec 10 '21 at 19:25
Looks like this has been asked and has several offered solutions: https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle will need to substitute - for , in answers though. I've always taken the recursive CTE approach but nice to see other possibilities. — xQbert, Dec 10 '21 at 19:26
This is a rather incomplete question and needs more context. Which version of SQL are you using? MS SQL? MySql or Oracle? @Neel please edit the question and tags to suite more what youre asking — Simon Price, Dec 10 '21 at 19:28

score 1 · Answer 1 · answered Dec 10 '21 at 19:52

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;

score 1 · Accepted Answer · answered Dec 10 '21 at 21:29

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

db<>fiddle here

score 0 · Answer 3 · edited Dec 10 '21 at 19:45

0

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.

edited Dec 10 '21 at 19:45

ouflak

2,458
10
44
49

answered Dec 10 '21 at 19:36

MiFi

1
1

score 0 · Answer 4 · answered Dec 10 '21 at 19:38

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>

SQL | SPLIT COLUMNS INTO ROWS

4 Answers4