Split Column with delimiter into multiple columns

Question

I got a table with 20 columns. One of the columns contains multiple values separated by a semicolon. It looks like this:

-9;-9;-1;-9;-9;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-1;-9;-1;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-1;-1;-1;-1;-9;-1;-1;-9;-9;-9;-9;-1;-9;-1;-9;-9;-9;-1;-9;-1;-9;-1;-9;-9;-9;-9;-1;-9;-9;-1;-1;-9;-1;-1;0000;FFF8;-9;-9;-9;-1;-9;-1;-9;FFF6;-9;-1;-9;-1;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9

It contains always 115 values. The values can be "-1", "-9" or a hex number.

Now I want to create a view which contains the original 20 columns plus 115 columns foreach delimited string. Can someone help me?

Tough luck. New to SQL and supposed to deal with data stored like that. — jarlh, Sep 14 '17 at 11:04

score 3 · Accepted Answer · answered Sep 14 '17 at 11:23

This is an example on how to do this:

DECLARE @tt TABLE(i INT IDENTITY,x VARCHAR(8000));
INSERT INTO @tt(x)VALUES('-9;-9;-1;-9;-9;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-1;-9;-1;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-1;-1;-1;-1;-9;-1;-1;-9;-9;-9;-9;-1;-9;-1;-9;-9;-9;-1;-9;-1;-9;-1;-9;-9;-9;-9;-1;-9;-9;-1;-1;-9;-1;-1;0000;FFF8;-9;-9;-9;-1;-9;-1;-9;FFF6;-9;-1;-9;-1;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9');

SELECT
    i,
    val1=n.v.value('/e[1]','VARCHAR(16)'),
    val2=n.v.value('/e[2]','VARCHAR(16)'),
    val3=n.v.value('/e[3]','VARCHAR(16)'),
    -- ... repeat for val4 .. val114
    val115=n.v.value('/e[115]','VARCHAR(16)')
FROM
    @tt
    CROSS APPLY (
        SELECT 
            CAST('<e>'+REPLACE(x,';','</e><e>')+'</e>' AS XML) AS itm
    ) AS i
    CROSS APPLY i.itm.nodes('/') AS n(v);

This is some XML trickery, by making the column with delimited values a XML where each value is an e element. The individual elements are then retrieved using the index in the value function.

Since this is a single statement it can be used as the query in a view.

score 2 · Answer 2 · answered Sep 14 '17 at 11:14

You can use a split function that returns the position. Unfortunately, split_string() in SQL Server 2016 does not yet support this. (Microsoft is aware of this.)

You can find code for such functions (say, here, although I don't recommend that particular version).

Then you can do:

select t.*, v.*
from t cross apply
     (select max(case when pos = 1 then val end) as col_001,
             max(case when pos = 2 then val end) as col_002,
             . . .
             max(case when pos = 115 then val end) as col_115
      from dbo.split(';', t.col) s(pos, val)
     ) v;

score 0 · Answer 3 · answered Sep 14 '17 at 11:24

You can query as below:

Declare @cols2 varchar(max)
Declare @query nvarchar(max)

--Row Numbers with tally
;with c1 as (
    Select top(Select max(len(splitcolumn) - len(replace(splitcolumn,';',''))) from #data) 
        RowN = Row_number() over (order by (Select NULL))
    from master..spt_values s1, master..spt_values 
)
select @cols2 = stuff((select ','+QuoteName(RowN) from c1 group by RowN for xml path('')),1,1,'')

Select @query = '
    Select * from (
        Select Id, RowN, [Value] from #data
        cross apply (
                Select xm = CAST(''<x>'' + REPLACE((SELECT REPLACE(splitcolumn,'';'',''$$$SSText$$$'') AS [*] FOR XML PATH('''')),''$$$SSText$$$'',''</x><x>'')+ ''</x>'' AS XML) 
        ) a
        cross apply (
            Select RowN = Row_Number() over (order by (SELECT NULL)), y.value(N''text()[1]'', N''nvarchar(MAX)'') as value 
            FROM a.xm.nodes(N''x'') as x(y) 
        ) b
    ) rws
    pivot (max([Value]) for RowN in (' + @cols2 + ')) p'

Exec sp_executesql @query

Split Column with delimiter into multiple columns

3 Answers3

Linked