SQL - separate string into different columns

Question

I am trying to separate strings into different columns which are separated by commas. I tried all the article that is on stackoverflow but not successful.

Example:

Column1
mouse,monitor,keyboard
cable,mouse
headset,desk,cable,monitor,usb,charger

Expected results:

Column1  |Column2  |Column3  |Column4  |Column5  |Column6 
mouse    |monitor  |keyboard | NULL    | NULL    | NULL
cable    |mouse    |NULL     | NULL    | NULL    | NULL
headset  |desk     |cable    | monitor | usb     | charger

Please note that the strings under Column1 can be as many as 10 strings and the strings are different every week so they are undefined.

This is one of the code I tried:

Declare #TblName (id int, Column1 varchar(max))

Insert into #TblName 

Select A.Column1
      ,B.*
 From  #TblNameK A
 Cross Apply (
        Select Pos1 = xDim.value('/x[1]','varchar(max)')
              ,Pos2 = xDim.value('/x[2]','varchar(max)')
              ,Pos3 = xDim.value('/x[3]','varchar(max)')
              ,Pos4 = xDim.value('/x[4]','varchar(max)')
              ,Pos5 = xDim.value('/x[5]','varchar(max)')
              ,Pos6 = xDim.value('/x[6]','varchar(max)')
              ,Pos7 = xDim.value('/x[7]','varchar(max)')
              ,Pos8 = xDim.value('/x[8]','varchar(max)')
              ,Pos9 = xDim.value('/x[9]','varchar(max)')
         From (Select Cast('<x>' + Replace(A.Column1,',','</x><x>')+'</x>' as XML) as xDim) A
       ) B

So you have 1 column with store data like that? and not knowing how many will be that string to separate into column? i think you are in trouble.. — dwir182
– dwir182, Commented Oct 18, 2018 at 4:21
You might need dynamic SQL to handle this. Do you need to preserve the word order in the CSV when converting to columns? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 18, 2018 at 4:22
@dwir182 yes, i have a column that store data like that. It's a column that stores the "search keywords" of our customers. — Jonathan
– Jonathan, Commented Oct 18, 2018 at 4:36
@TimBiegeleisen - after splitting the strings, I will have to "Group By" them. — Jonathan
– Jonathan, Commented Oct 18, 2018 at 4:39

iSR5 · Accepted Answer · 2018-10-18 10:50:58Z

1

You can use XML method below :

DECLARE 
    @t TABLE (keywords VARCHAR(MAX) ) 


INSERT INTO @t VALUES 
('mouse,monitor,keyboard'),
('cable,mouse'),
('headset,desk,cable,monitor,usb,charger'), 
('M&M,Hot&Cold,sneakers')



SELECT 
    ROW_NUMBER() OVER(ORDER BY keywords DESC) ID 
,   keywords 
FROM (
    SELECT 
        LTRIM(RTRIM(m.n.value('.[1]','VARCHAR(8000)'))) keywords
    FROM (
        SELECT CAST('<Root><Keyword>' + REPLACE(REPLACE(keywords,'&','&amp;') ,',','</Keyword><Keyword>') + '</Keyword></Root>' AS XML) keywords
        FROM @t
    ) D
    CROSS APPLY keywords.nodes('/Root/Keyword')m(n)
) C

This will put each keyword in a row. From there you can count the number of keywords and do further stuff on them (like getting the distinct values, pivot them ..etc).

edited Oct 18, 2018 at 10:50

answered Oct 18, 2018 at 8:32

iSR5

3,5492 gold badges16 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Over a year ago

I am getting " Msg 9411, Level 16, State 1, Line 1 XML parsing: line 1, character 20, semicolon expected "

iSR5 Over a year ago

@Jonathan is your data has some <,>, & characters ?

Roger Wolf · Accepted Answer · 2018-10-18 06:55:13Z

1

Since you are using SQL Server 2016, you can use the built-in string_split() function:

declare @t table (Value varchar(max));

insert into @t (Value)
values
    ('mouse,monitor,keyboard'),
    ('cable,mouse'),
    ('headset,desk,cable,monitor,usb,charger')
;

select *
from @t t
    cross apply string_split(t.Value, ',') ss;

Having all the values in one column will be especially handy if you are actually going to get some aggregated statistics out of them.

answered Oct 18, 2018 at 6:55

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

8 Comments

Jonathan Over a year ago

under values, you've written the example strings given above, what if i don't know what are the strings? or the number of rows?

Serg Over a year ago

@Jonathan Just check code in this answer with your own data sets.

Jonathan Over a year ago

@Serg I am getting Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.

Serg Over a year ago

@Jonathan What tool and how are you using to run this code?

Jonathan Over a year ago

@Serg oh i get it now. ran the code but getting " invalid object name 'string_split' "

|

Collectives™ on Stack Overflow

SQL - separate string into different columns

2 Answers 2

2 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related