CODEWITHSUNDEEP
sqlsql-server
Builder
Builder

Reputation: 1056

Comma separated string into two column

I need a solution from a comma separated string. I have a function which returns a list of rows of one column

declare @datap varchar(200)
set @datap = 'KIA,A,TEST1,I,KIA2,A,KIA2,A,KIA3,I'
select * from dbo.fs_1_fn_split(@datap, ',')

it gives in this form or also in one column named value as showing in column 2

id  value
1   KOA
2   A
3   TEST1
4   I
5   KIA2
6   A
7   KIA2
8   A
9   KIA3
10  I

well I need to convert this in two columns as

KOA    A
TEST1  I
KIA2   A
.....

Upvotes: 0

Views: 86

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this by aggregating the results after you split out the values. Here is one approach:

select max(case when id % 2 = 1 then value end) as value1,
       max(case when id % 2 = 0 then value end) as value2       
from (select id, value, (id-1)/2 as grp
      from dbo.fs_1_fn_split(@datap, ',') 
     ) t
group by grp

Upvotes: 4

Related Questions