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, Oct 18 '18 at 04: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, Oct 18 '18 at 04: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, Oct 18 '18 at 04:36
@TimBiegeleisen - after splitting the strings, I will have to "Group By" them. — Jonathan, Oct 18 '18 at 04:39
@Jonathan you want to split them into columns then Group By them ? how come? isn't faster if you just put them into one column and then select distinct values, which will give you unique values ? a more explanation on why you need to do that in which will help us to understand the best approach for your case — iSR5, Oct 18 '18 at 05:48
Have you checked this? https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Faran Saleem, Oct 18 '18 at 06:06
@iSR5 the reason for that is i need to the a "count" each strings. — Jonathan, Oct 18 '18 at 06:28
@FaranSaleem Yes i did, but that's only if you know how many strings there would be in a row. How about if it's indefinite? — Jonathan, Oct 18 '18 at 06:29
For an unknown number of columns I agree with Tim, as far as I know you will have to use dynamic sql. If you have a max number of columns you can use the xml trick to get the columns you want. — Zohar Peled, Oct 18 '18 at 08:38

score 1 · Answer 1 · answered Oct 18 '18 at 06:55

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 '18 at 06:55

Roger Wolf

7,307
2
24
33

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? – Jonathan Oct 18 '18 at 07:07
@Jonathan Just check code in this answer with your own data sets. – Serg Oct 18 '18 at 07:36
@Serg I am getting Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('. – Jonathan Oct 18 '18 at 07:46
@Jonathan What tool and how are you using to run this code? – Serg Oct 18 '18 at 08:08
@Serg oh i get it now. ran the code but getting " invalid object name 'string_split' " – Jonathan Oct 18 '18 at 08:19
@Jonathan Could you please run the following code and provide its output here: `select @@version;` – Serg Oct 18 '18 at 08:24
@Serg Microsoft SQL Server 2016 (SP1-CU8) (KB4077064) - 13.0.4474.0 (X64) Feb 24 2018 13:53:17 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor) – Jonathan Oct 18 '18 at 08:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/182077/discussion-between-serg-and-jonathan). – Serg Oct 18 '18 at 08:51

iSR5 · Accepted Answer · 2018-10-18T10:50:58.647

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).

I am getting " Msg 9411, Level 16, State 1, Line 1 XML parsing: line 1, character 20, semicolon expected " — Jonathan, Oct 18 '18 at 09:10

SQL - separate string into different columns

2 Answers2