CODEWITHSUNDEEP
sqlsql-server
Sugandh Agarwal
Sugandh Agarwal

Reputation: 11

How to Split two column in same table in SQL Sever

Declare @abc varchar(max)='5,4,1',
@xyz varchar(max)='1,2,3';

select value[A] from  string_split(@abc,',')
select value[B] from  string_split(@xyz,',')

I need this result in same table

A   B
5   1
4   2
1   3

Upvotes: 1

Views: 94

Answers (1)

DarkRob
DarkRob

Reputation: 3833

You may use row_number() to give unique number to each record of your string, then apply join on behalf of that generated row_number().
Sample is like this.

   select [A], [B] from 
   (
       select row_number() over (order by (select 100)) as Slno, value as  [A] from  string_split(@abc,',')
   ) as t1 
   full outer join 
   (
        select row_number() over (order by (select 100)) as Slno, value as  [B] from  string_split(@xyz,',')
   ) as t2 
   on t1.slno=t2.slno

Upvotes: 1

Related Questions