SQLite 3.16

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

create table tb (item integer, num integer, sn text);
insert into tb (item, num, sn) values (1,11,'a'),(2,11,'b'),(3,11,'c'),(4,11,'d'),(5,11,'e'),(6,11,'f'),(7,11,'g'),(8,11,'h'),(9,11,'i'),(10,11,'j'),(11,11,'k'),(12,11,'L');
select * from tb;


item
num
sn


1
11
a

2
11
b

3
11
c

4
11
d

5
11
e

6
11
f

7
11
g

8
11
h

9
11
i

10
11
j

11
11
k

12
11
L


select (item -1)/5 as q  , (item - (item -1)/5*5) as mod  ,sn from tb;


q
mod
sn


0
1
a

0
2
b

0
3
c

0
4
d

0
5
e

1
1
f

1
2
g

1
3
h

1
4
i

1
5
j

2
1
k

2
2
L


select main.q, coalesce(a.sn,'') as m1, coalesce(b.sn,'') as m2, coalesce(c.sn,'') as m3, coalesce(d.sn,'') as m4, coalesce(e.sn,'') as m5
from
(select  (item -1)/5 as q from tb group by q) main
left join (select (item -1)/5 as q  , (item - (item -1)/5*5) as mod  ,sn from tb where mod =1) a on main.q=a.q
left join (select (item -1)/5 as q  , (item - (item -1)/5*5) as mod  ,sn from tb where mod =2) b on main.q=b.q
left join (select (item -1)/5 as q  , (item - (item -1)/5*5) as mod  ,sn from tb where mod =3) c on main.q=c.q
left join (select (item -1)/5 as q  , (item - (item -1)/5*5) as mod  ,sn from tb where mod =4) d on main.q=d.q
left join (select (item -1)/5 as q  , (item - (item -1)/5*5) as mod  ,sn from tb where mod =5) e on main.q=e.q


q
m1
m2
m3
m4
m5


0
a
b
c
d
e

1
f
g
h
i
j

2
k
L





select tb.*, m1||m2||m3||m4||m5 as sn_total
from tb
  left join(
    select main.q, coalesce(a.sn||',','') as m1, coalesce(b.sn||',','') as m2, coalesce(c.sn||',','') as m3, coalesce(d.sn||',','') as m4, coalesce(e.sn||',','') as m5
    from
    (select  (item -1)/5 as q from tb group by q) main
    left join (select (item -1)/5 as q, sn from tb where (item - (item -1)/5*5) = 1) a on main.q=a.q
    left join (select (item -1)/5 as q, sn from tb where (item - (item -1)/5*5) = 2) b on main.q=b.q
    left join (select (item -1)/5 as q, sn from tb where (item - (item -1)/5*5) = 3) c on main.q=c.q
    left join (select (item -1)/5 as q, sn from tb where (item - (item -1)/5*5) = 4) d on main.q=d.q
    left join (select (item -1)/5 as q, sn from tb where (item - (item -1)/5*5) = 5) e on main.q=e.q
  ) g 
  on (tb.item - 1) / 5 = g.q


item
num
sn
sn_total


1
11
a
a,b,c,d,e,

2
11
b
a,b,c,d,e,

3
11
c
a,b,c,d,e,

4
11
d
a,b,c,d,e,

5
11
e
a,b,c,d,e,

6
11
f
f,g,h,i,j,

7
11
g
f,g,h,i,j,

8
11
h
f,g,h,i,j,

9
11
i
f,g,h,i,j,

10
11
j
f,g,h,i,j,

11
11
k
k,L,

12
11
L
k,L,

item	num	sn	sn_total
1	11	a	a,b,c,d,e,
2	11	b	a,b,c,d,e,
3	11	c	a,b,c,d,e,
4	11	d	a,b,c,d,e,
5	11	e	a,b,c,d,e,
6	11	f	f,g,h,i,j,
7	11	g	f,g,h,i,j,
8	11	h	f,g,h,i,j,
9	11	i	f,g,h,i,j,
10	11	j	f,g,h,i,j,
11	11	k	k,L,
12	11	L	k,L,