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

Question

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!

John Cappelletti · Accepted Answer · 2016-11-21 23:45:38Z

3

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"}]

edited Nov 21, 2016 at 23:45

answered Nov 21, 2016 at 23:20

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

5 Comments

Zed Fang Over a year ago

Thank you for your answer. However, when I ran your code above. The messages show me "Cannnot find data type 'XML'". One answer I found why this happens is XML seems to just be compatible under 2005/2008 sql server, while I am using 2016. Nevertheless, when I change the script version back to 2005/2008, it is still not working. Any ideas?

John Cappelletti Over a year ago

@ZedFang XML was available since version 2005. I simply can't imagine where there error is coming from. I'll do a little more digging.

Zed Fang Over a year ago

Sounds good. Anyway, the solution you used above is just for the original columns which has just utmost three ',{'. However, if I don't know how many ',{' in each row, how can I automatically separate this columns into several columns based each row itself? I am used to using split_part under postgresql, which will automatically detect the separator. I don't believe sql server has this function either.

John Cappelletti Over a year ago

@ZedFang The question was not clear if you needed dynamic. However, it it may be easier just to expand the cross apply to say 10. Just copy actions3 line and change the 3 to 4 on both sides of the equals.

Zed Fang Over a year ago

I know why your syntax didn't work earlier. It is because my data is sitting under Views. I believe your syntax can work for most of the SQL server cases. Thanks again

Zed Fang · Accepted Answer · 2016-12-04 21:34:47Z

@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

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related