CODEWITHSUNDEEP
sql-serversplit
Zed Fang
Zed Fang

Reputation: 883

SQL Server: split a column into multiple columns (tabular format)

I have a table that looks like this: 

id         actions
ua123      [{'type':'mobile'},{'action':'display conversion'}]
ua234      [{'type':'DT'},{'action':'search'},{'value':'40'}]

Why this table looks like this is because the information was crawled from the website. And I want to split the actions column into several columns, such as:

id         actions1                    action2                         action3
ua123      [{'type':'mobile'}   {'action':'display conversion'}]        Null
ua234      [{'type':'DT'}       {'action':'search'}                {'value':'40'}]

Please feel free to share any lights. Thanks!

Upvotes: 2

Views: 412

Answers (2)

Zed Fang
Zed Fang

Reputation: 883

@johncappelletti Thank you for your answer, which gave me some light!

Here is my final syntax I used to solve my problem. 

Declare @delimiter varchar(50)
set @delimiter=' ';

With Test1 as 
(Select id,
        actions,
        cast('<x>'+replace(actions,@delimiter,'<x></x>')+'</x>' as XML)
        as Name_XML
 From tb1
)
Select id,
       actions,
       Name_XML.value('/x[1]','varchar(50)') as action1,
       Name_XML.value('/x[2]','varchar(50)') as action2,
       Name_XML.value('/x[3]','varchar(50)') as action3,
       Name_XML.value('/x[4]','varchar(50)') as action4,
         .
         .
         .
         .
From Test1

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81960

Assuming you don't want dynamic. (Small change if needed)

Select A.ID
      ,B.*
 From  YourTable A
 Cross Apply (
                Select actions1 = xDim.value('/x[1]','varchar(max)')
                      ,actions2 = '{'+xDim.value('/x[2]','varchar(max)')
                      ,actions3 = '{'+xDim.value('/x[3]','varchar(max)')
                 From (Select Cast('<x>' + Replace(A.Actions,',{','</x><x>')+'</x>' as XML) as xDim) A
             ) B

Returns

ID       actions1           actions2                            actions3
ua123   [{"type":"mobile"}  {"action":"display conversion"}]    NULL
ua234   [{"type":"DT"}      {"action":"search"}                {"value":"40"}]

Upvotes: 3

Related Questions