How to split a cell and create a new row in sql

Question

I have a column which stores multiple comma separated values. I need to split it in a way so that it gets split into as many rows as values in that column along with remaining values in that row.

eg:

John 111 2Jan
Sam  222,333 3Jan
Jame 444,555,666 2Jan
Jen  777 4Jan

Output:

John 111 2Jan
Sam  222 3Jan
Sam  333 3Jan
Jame 444 2Jan
Jame 555 2Jan
Jame 666 2Jan
Jen  777 4Jan

P.S : I have seen multiple questions similar to this, but could not find a way to split in such a way.

If using oracle you can split string using method explained https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement ... should be able to do on one sql statement ... good luck — Ruskin, Jan 27 '17 at 10:36
P.S. fix your data structure, normalize your tables, be happy ever after. — Gurwinder Singh, Jan 27 '17 at 10:38
With SQL Server you can use a numbers table as described by Jeff Moden on SQLServerCentral at http://www.sqlservercentral.com/articles/Tally+Table/72993/ (may require registration ... worth it) — Ruskin, Jan 27 '17 at 10:38
The data is being pulled from multiple tables involving may Joins. It is just this column that has such values. — Manas Arora, Jan 27 '17 at 10:41
Never, ever have comma separated values. They will only cause you lots of trouble! — jarlh, Jan 27 '17 at 12:58

score 0 · Answer 1 · edited May 23 '17 at 12:33

This solution is built on Vertica, but it works for every database that offers a function corresponding to SPLIT_PART().

Part of it corresponds to the un-pivoting technique that works with every ANSI compliant database platform that I explain here (just the un-pivoting part of the script):

Pivot sql convert rows to columns

So I would do it like here below. I'm assuming that the minimalistic date representation is part of the second column of a two-column input table. So I'm first splitting that short date literal away, in a first Common Table Expression (and, in a comment, I list that CTE's output), before splitting the comma separated list into tokens.

Here goes:

WITH
-- input
input(name,the_string) AS (
          SELECT 'John', '111 2Jan'
UNION ALL SELECT 'Sam' , '222,333 3Jan'
UNION ALL SELECT 'Jame', '444,555,666 2Jan'
UNION ALL SELECT 'Jen' , '777 4Jan'
)
,
-- put the strange date literal into a separate column
the_list_and_the_date(name,list,datestub) AS (
SELECT
  name
, SPLIT_PART(the_string,' ',1)
, SPLIT_PART(the_string,' ',2)
FROM input
)
-- debug
-- SELECT * FROM the_list_and_the_date;
-- name|list       |datestub
-- John|111        |2Jan
-- Sam |222,333    |3Jan
-- Jame|444,555,666|2Jan
-- Jen |777        |4Jan
,
-- ten integers (too many for this example) to use as pivoting value and as "index"
ten_ints(idx) AS (
          SELECT  1 
UNION ALL SELECT  2 
UNION ALL SELECT  3 
UNION ALL SELECT  4 
UNION ALL SELECT  5 
UNION ALL SELECT  6 
UNION ALL SELECT  7 
UNION ALL SELECT  8 
UNION ALL SELECT  9 
UNION ALL SELECT 10
)
-- the final query - pivoting prepared input using a CROSS JOIN with ten_ints
-- and filter out where the SPLIT_PART() expression evaluates to the empty string
SELECT
  name
, SPLIT_PART(list,',',idx) AS token
, datestub
FROM the_list_and_the_date
CROSS JOIN ten_ints
WHERE SPLIT_PART(list,',',idx) <> ''
;

name|token|datestub
John|111  |2Jan
Jame|444  |2Jan
Jame|555  |2Jan
Jame|666  |2Jan
Sam |222  |3Jan
Sam |333  |3Jan
Jen |777  |4Jan

Happy playing ...

Marco the Sane

How to split a cell and create a new row in sql

1 Answers1