CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Jonathan
Jonathan

Reputation: 172

SQL - separate string into different columns

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

Upvotes: 1

Views: 116

Answers (2)

iSR5
iSR5

Reputation: 3498

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

Upvotes: 1

Roger Wolf
Roger Wolf

Reputation: 7692

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.

Upvotes: 1

Related Questions