SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE itemTable (A_NUMBER int,items varchar(200));
INSERT INTO itemTable (A_NUMBER, items)
VALUES
  (1, 'i1,i2,i3,i4,i5,i6'),
  (2, 'j1,j2,j3,j4,j5,i6,i7,i8'),
  (3, 'k1,k2,k3'),
  (4, 'k1,k2'),
  (5, 'k1'),
  (6, 'k1,k2,k3,k4'),
  (7, '');
SELECT * FROM ItemTable


A_NUMBER
items


1
i1,i2,i3,i4,i5,i6

2
j1,j2,j3,j4,j5,i6,i7,i8

3
k1,k2,k3

4
k1,k2

5
k1

6
k1,k2,k3,k4

7



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


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



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
order by a_number


A_NUMBER
items
Srllno1
Srllno2
Srllno3
Srllno4
SrllRest


1
i1,i2,i3,i4,i5,i6
i1
i2
i3
i4
5,i6

2
j1,j2,j3,j4,j5,i6,i7,i8
j1
j2
j3
j4
5,i6,i7,i8

3
k1,k2,k3
k1
k2
k3
null


4
k1,k2
k1
k2
null
null


5
k1
k1
null
null
null


6
k1,k2,k3,k4
k1
k2
k3
k4


7


null
null
null



Warning: Null value is eliminated by an aggregate or other SET operation.


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
order by a_number
Msg 195 Level 15 State 10 Line 6
'string_agg' is not a recognized built-in function name.
Msg 102 Level 15 State 1 Line 8
Incorrect syntax near 'i'.

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
order by a_number
Msg 195 Level 15 State 10 Line 6
'string_agg' is not a recognized built-in function name.
Msg 8144 Level 16 State 3 Line 8
Procedure or function string_split has too many arguments specified.
Msg 102 Level 15 State 1 Line 8
Incorrect syntax near 'i'.

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

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

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