CODEWITHSUNDEEP
oracle-databasesnowflake-cloud-data-platform
Neel
Neel

Reputation: 23

SQL | SPLIT COLUMNS INTO ROWS

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

Upvotes: 1

Views: 811

Answers (4)

MT0
MT0

Reputation: 168613

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

Upvotes: 1

JNevill
JNevill

Reputation: 50263

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;

Upvotes: 1

MiFi
MiFi

Reputation: 1

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.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143073

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>

Upvotes: 0

Related Questions