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');

SELECT * FROM ItemTable

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 itemtable;


A_NUMBER
items


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

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

3
k1,k2,k3




A_NUMBER
items
Srllno1
Srllno2
Srllno3
Srllno4


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

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

3
k1,k2,k3
k3
k2
k1
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


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



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
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
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	Srllno1	Srllno2	Srllno3	Srllno4
1	i1,i2,i3,i4,i5,i6	null	null	null	null
2	j1,j2,j3,j4,j5,i6,i7,i8	null	null	null	null
3	k1,k2,k3	k3	k2	k1	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