How to apply string split function on multiple columns in SQL Server

Question

I have a data like this, please help

id  | name    | value
----+---------+---------
1   | a, b    | 12, 34

I want to get an output like this:

id  | name  | value
----+-------+---------
 1  | a     |  12  
 1  | b     |  34

FYI: you might consider normalizing your table. Then you wouldn't have to do these types of splits. — devlin carnate
– devlin carnate, Commented Apr 29, 2021 at 18:11
Are those comma separated strings always pairs? Or can there be more than 2 items e.g. name of "a, b, c" and value of "12, 43, 56" — Paul Maxwell
– Paul Maxwell, Commented Apr 29, 2021 at 21:08
paul: they aren't always in pairs , if we have 100 rows, some rows contain only 1 value, some contain 2, some contain 3, so its not Static, — Baby boy
– Baby boy, Commented May 1, 2021 at 14:46

John Cappelletti · Accepted Answer · 2021-04-29 18:14:39Z

1

Just another option (Gordon is way too fast for me)

The JSON [Key] is a proper sequence... assuming both columns have the same number of elements.

Example

Declare @YourTable Table ([id] varchar(50),[name] varchar(50),[value] varchar(50))  
Insert Into @YourTable Values 
 (1,'a,b','12,34')
 
Select ID
      ,Name  = max(case when Item='Name'  then B.Value end)
      ,Value = max(case when Item='Value' then B.Value end)
From @YourTable A
Cross Apply (
                Select [Key]
                      ,Item = 'Name'
                      ,Value
                 From  OpenJSON( '["'+replace(replace([name],'"','\"'),',','","')+'"]' )
                Union All
                Select [Key]
                      ,Item = 'Value'
                      ,Value
                 From  OpenJSON( '["'+replace(replace([value],'"','\"'),',','","')+'"]' )

             ) B
 Group By ID,[Key]

Results

ID  Name    Value
1   a       12
1   b       34

answered Apr 29, 2021 at 18:14

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

2 Comments

Baby boy Over a year ago

john: this works for the scenario mentioned above, but in reality i have 10 columns having values seperated by a delimiter

John Cappelletti Over a year ago

@Babyboy It would still work... you would just need to add 8 more UNION ALL's. It would get a little ugly, but there is always a penalty for design flaws.

Gordon Linoff · Accepted Answer · 2021-04-29 18:08:02Z

One method is a recursive CTE:

with cte as (
      select id, convert(varchar(max), null) as name, convert(varchar(max), null) as val, 
             convert(varchar(max), names + ',') as names, convert(varchar(max), vals + ',') as vals
      from t
      union all
      select id,
             convert(varchar(max), left(names, charindex(',', names) - 1)),
             convert(varchar(max), left(vals, charindex(',', vals) - 1)),
             convert(varchar(max), stuff(names, 1, charindex(',', names), '')),
             convert(varchar(max), stuff(vals, 1, charindex(',', vals), ''))
      from cte
      where names like '%,%' and vals like '%,%'
     )
select id, name, val
from cte
where name is not null;

Here is a db<>fiddle.

Collectives™ on Stack Overflow

How to apply string split function on multiple columns in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related