How to split multiple comma-separated value that are in columns to new columns

Question

I got the same / similar problem as in 'How to split a comma-separated value to columns' , but my solution code needs to be more flexible.

I got multiple (N) columns with comma separated values. E.G.:

Column1        Column2
======================
a1, a2, a3     er, asw, as
a2, a3         qwe, qw

What I now need is this

Column1_1 Column1_2 Columm1_3   Column2_1 Column2_2 Column2_3
==================================================================
a1        a2        a3          er        asw       as
a2        a3        NULL        qwe       qw        NULL

So depending on the columns "longest" (most by comma seperated values) I need new Columns with the same name and an index (here 1-3). If one cell has less than the maximum values (e.g. in this case 2) the remaining new columns should not contain any values respecectively NULL.

I hope someone could help me with this!

Thanks a lot!

EDIT:
I tried using this code. It works so far, but its not flexible and only taking one column instead of "N columns"

DECLARE @xml xml

SELECT @xml = (
SELECT CAST(
'<i id="' + CAST(COLUMN1 as nvarchar(10)) + '"><w>' + REPLACE(NID,' ','</w>    <w>') + '</w></i>' as xml)
FROM table4
FOR XML PATH('')
)

SELECT  
    t.v.value('w[1]','nvarchar(100)') as String1,
    t.v.value('w[2]','nvarchar(100)') as String2,
    t.v.value('w[3]','nvarchar(100)') as String3,
    t.v.value('w[4]','nvarchar(100)') as String4,
    t.v.value('w[5]','nvarchar(100)') as String5,
    t.v.value('w[6]','nvarchar(100)') as String6,
    t.v.value('w[7]','nvarchar(100)') as String7
FROM @xml.nodes('/i') as t(v)

Have you tried anything to solve this issue? You have a good starting point, so what is tripping you up? — dfundako, Jul 06 '16 at 13:32
Count commas in your columns to find max. Add to columns remaining commas. After that contact your two column and use the method, described in your link. — xdd, Jul 06 '16 at 13:35
Hopefully this is a normalization project. If not, you need to read up on normalization because storing delimited data like this violates 1NF and causes untold amounts of pain to work with. — Sean Lange, Jul 06 '16 at 13:42
@mgruber What does *only taking one column instead of N columns* mean? Don't you even know how many columns you'll have? Or do you need the count of elements for each column dynamically found? If there are 10 columns and the one with the highest count of elements is `a,b,c,d,e`, you'd need a result set of 50 columns each of the number from `1_1 to 1_5 up tp 10_1 to 10_5`? If so, you really should re-consider your approach... — Shnugo, Jul 06 '16 at 13:54
Yeah I need the count of elements dynamically found. Basically I need to find the entry with the highest count and depending on that is the number of new columns 1_1 to 1_N Lets say there is a maximum of 2 Columns which could have up to 10 elements a,b,c,d,e — mgruber, Jul 06 '16 at 14:01
@mgruber If so, you might take my approach and add the positions `4 to 10` for both columns. Many columns will return with `NULL`... — Shnugo, Jul 06 '16 at 14:04
Seems like the easiest way ... but there will be a lot of cells and probably some columns with NULL — mgruber, Jul 06 '16 at 14:07
@mgruber Anyway you'll have to deal with the result somehow... It is much easier to deal with **a known structure with many empty cells** than with one you don't know about. The only way to achieve a resultset with a dynmic count (and naming) of columns was a dynamically built cmd which you execute with `EXEX` or `sp_execute...`. But there are several backdraws with this... — Shnugo, Jul 06 '16 at 14:32

score 3 · Answer 1 · answered Jul 06 '16 at 13:40

Try it like this:

It might be necessary to wrap all output columns with LTRIM(RTRIM(...)) to get rid of trailing spaces.

And it might be necessary to replace the three characters of evil, if you expect them to be within your strings (<>; should be < > and &)

DECLARE @tbl TABLE(Column1 VARCHAR(100),Column2 VARCHAR(100));
INSERT INTO @tbl VALUES
 ('a1, a2, a3','er, asw, as')
,('a2, a3','qwe, qw');

WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(Column1,',','</x><x>') + '</x>' AS XML) Col1Xml
          ,CAST('<x>' + REPLACE(Column2,',','</x><x>') + '</x>' AS XML) Col2Xml
    FROM @tbl
)
SELECT Col1Xml.value('/x[1]','varchar(max)') AS Column1_1
      ,Col1Xml.value('/x[2]','varchar(max)') AS Column1_2 
      ,Col1Xml.value('/x[3]','varchar(max)') AS Column1_3 
      ,Col2Xml.value('/x[1]','varchar(max)') AS Column2_1
      ,Col2Xml.value('/x[2]','varchar(max)') AS Column2_2 
      ,Col2Xml.value('/x[3]','varchar(max)') AS Column2_3  
FROM Splitted

How to split multiple comma-separated value that are in columns to new columns

1 Answers1