Converting nth delimiter in CSV string into columns

Question

I have the strings in db where the serial numbers of items are together as shown here

itemTable

A_NUMBER	items
1	i1,i2,i3,i4,i5,i6
2	j1,j2,j3,j4,j5,i6,i7,i8
3	k1,k2,k3

I want the resultant output of this string as shown below:

Srllno1	Srllno2	Srllno3	Srllno4	SrllRest
i1	i2	i3	i4	i5,i6
j1	j2	j3	j4	j5,i6,i7,i8
k1	k2	k3

I tried this SQL statement:

SELECT
    A_NUMBER,
    items,
    PARSENAME(REPLACE(items, ',', '.'), 1) AS Srllno1,
    PARSENAME(REPLACE(items, ',', '.'), 2) AS Srllno2,
    PARSENAME(REPLACE(items, ',', '.'), 3) AS Srllno3,
    PARSENAME(REPLACE(items, ',', '.'), 4) AS Srllno4
FROM
    db.itemtable;

But I could not get the SrlRest unless I did it manually.

I also tried with STRING_SPLIT but I wasn't successful.

CRME: https://dbfiddle.uk/i_N273aI

How to achieve the desired result ?

Answer 1

See example with STRING_SPLIT

select A_NUMBER,min(items) items
  ,max(case when ordinal=1 then value end)Srllno1
  ,max(case when ordinal=2 then value end)Srllno2
  ,max(case when ordinal=3 then value end)Srllno3
  ,max(case when ordinal=4 then value end)Srllno4
  ,string_agg(case when ordinal>4 then value end,',') within group (order by ordinal)SrllRest
from itemTable
cross apply(select value,ordinal from string_split(items,',',1))i
group by A_NUMBER

output is

A_NUMBER	items	Srllno1	Srllno2	Srllno3	Srllno4	SrllRest
1	i1,i2,i3,i4,i5,i6	i1	i2	i3	i4	i5,i6
2	j1,j2,j3,j4,j5,i6,i7,i8	j1	j2	j3	j4	j5,i6,i7,i8
3	k1,k2,k3	k1	k2	k3	null	null

Demo

Thats suprise:) For SQL Server 2017 and later

select A_NUMBER,min(items) items
  ,max(case when ordinal=1 then value end)Srllno1
  ,max(case when ordinal=2 then value end)Srllno2
  ,max(case when ordinal=3 then value end)Srllno3
  ,max(case when ordinal=4 then value end)Srllno4
  ,string_agg(case when ordinal>4 then value end,',') within group (order by ordinal)SrllRest
from itemTable
cross apply(select value,row_number()over(order by (select null))ordinal from string_split(items,','))i
group by A_NUMBER

ordinal replaced by row_number()over(order by (select null)

And for SQL Server 2016

select A_NUMBER,items
  ,max(case when ordinal=1 then value end)Srllno1
  ,max(case when ordinal=2 then value end)Srllno2
  ,max(case when ordinal=3 then value end)Srllno3
  ,max(case when ordinal=4 then value end)Srllno4
  ,COALESCE(STUFF(
   (select ','+value from(select value,row_number()over(order by (select null))ordinal 
                            from string_split(items,','))i
    where ordinal>4
     for XML PATH('')
    ), 1, 2, N''
  ), N'')SrllRest
from itemTable
cross apply(select value,row_number()over(order by (select null))ordinal from string_split(items,','))i
group by A_NUMBER,items

string_agg replaced by select ... XML Path
demo

Version for SQL Server 2008

with r as(
  select 1 lvl, A_NUMBER,items
    ,case when charindex(',',items)>0 then
        substring(items,1,charindex(',',items)-1)
        when len(items)>0 then items
     else null
     end SrllNo1
    ,cast(null as varchar) SrllNo2,cast(null as varchar) SrllNo3,cast(null as varchar) SrllNo4
    ,case when charindex(',',items)>0 then
        substring(items,charindex(',',items)+1,1000)
     else ''
     end SrllRest
  from itemTable
  union all
  select lvl+1 lvl, A_NUMBER,items
    ,SrllNo1
    ,cast(case when lvl=1 and  charindex(',',SrllRest)>0 then
             substring(SrllRest,1,charindex(',',SrllRest)-1)
           when lvl=1 and  len(SrllRest)>0 then SrllRest
     else SrllNo2
     end as varchar) SrllNo2
    ,cast(case when lvl=2 and  charindex(',',SrllRest)>0 then
             substring(SrllRest,1,charindex(',',SrllRest)-1)
           when lvl=2 and  len(SrllRest)>0 then SrllRest
     else SrllNo3
     end as varchar) SrllNo3
    ,cast(case when lvl=3 and  charindex(',',SrllRest)>0 then
             substring(SrllRest,1,charindex(',',SrllRest)-1)
           when lvl=3 and  len(SrllRest)>0 then SrllRest
     else SrllNo4
     end as varchar) SrllNo4
    ,case when charindex(',',SrllRest)>0 then
        substring(SrllRest,charindex(',',SrllRest)+1,1000)
     else ''
     end SrllRest
  from r where len(SrllRest)>0 and lvl<4
)
select * from r
where lvl=4 or len(SrllRest)=0
order by a_number

demo

lvl	A_NUMBER	items	SrllNo1	SrllNo2	SrllNo3	SrllNo4	SrllRest
4	1	i1,i2,i3,i4,i5,i6	i1	i2	i3	i4	i5,i6
4	2	j1,j2,j3,j4,j5,i6,i7,i8	j1	j2	j3	j4	j5,i6,i7,i8
3	3	k1,k2,k3	k1	k2	k3	null
2	4	k1,k2	k1	k2	null	null
1	5	k1	k1	null	null	null
4	6	k1,k2,k3,k4	k1	k2	k3	k4
1	7		null	null	null	null