MySQL 8.0

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

create table test (
id int,
page_id varchar(2),
name varchar(255));

insert into test values 
(1,'P1','a,b,c,d,e'),
(1,'P2','f,h,z,a,c'),
(1,'P3','x,y,z,g,c'),
(2,'P1','a,z'),
(2,'P2','a,c,v,b');


create table numbers (
nr int );

insert into numbers values 
(1),
(2),
(3),
(4),
(5),
(6);
select id,
       page_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1) as name
from numbers 
inner join test on CHAR_LENGTH(test.name)-CHAR_LENGTH(REPLACE(test.name, ',', ''))>=numbers.nr-1
group by id,page_id,SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1)
order by id asc;


id
page_id
name


1
P1
a

1
P1
b

1
P1
c

1
P1
d

1
P1
e

1
P2
a

1
P2
c

1
P2
f

1
P2
h

1
P2
z

1
P3
c

1
P3
g

1
P3
x

1
P3
y

1
P3
z

2
P1
a

2
P1
z

2
P2
a

2
P2
b

2
P2
c

2
P2
v


select id,name,count(distinct page_id,name) as page_count
from (
       select id,
       page_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1) as name
from numbers 
inner join test on CHAR_LENGTH(test.name)-CHAR_LENGTH(REPLACE(test.name, ',', ''))>=numbers.nr-1
group by id,page_id,SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1)
     ) as t1
group by id,name
order by id asc;


id
name
page_count


1
a
2

1
b
1

1
c
3

1
d
1

1
e
1

1
f
1

1
g
1

1
h
1

1
x
1

1
y
1

1
z
2

2
a
2

2
b
1

2
c
1

2
v
1

2
z
1

id	page_id	name
1	P1	a
1	P1	b
1	P1	c
1	P1	d
1	P1	e
1	P2	a
1	P2	c
1	P2	f
1	P2	h
1	P2	z
1	P3	c
1	P3	g
1	P3	x
1	P3	y
1	P3	z
2	P1	a
2	P1	z
2	P2	a
2	P2	b
2	P2	c
2	P2	v

id	page_id	name
1	P1	a
1	P1	b
1	P1	c
1	P1	d
1	P1	e
1	P2	a
1	P2	c
1	P2	f
1	P2	h
1	P2	z
1	P3	c
1	P3	g
1	P3	x
1	P3	y
1	P3	z
2	P1	a
2	P1	z
2	P2	a
2	P2	b
2	P2	c
2	P2	v

id	page_id	name
1	P1	a
1	P1	b
1	P1	c
1	P1	d
1	P1	e
1	P2	a
1	P2	c
1	P2	f
1	P2	h
1	P2	z
1	P3	c
1	P3	g
1	P3	x
1	P3	y
1	P3	z
2	P1	a
2	P1	z
2	P2	a
2	P2	b
2	P2	c
2	P2	v