MySQL 8.0

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

select version();


version()


8.0.36


CREATE TABLE mytable (
   Genres  VARCHAR(2000));

INSERT INTO mytable VALUES
('Classics, Fiction, Historical Fiction, School, Literature, Young Adult, Historical'),
('Fantasy, Fiction, Young Adult, Magic, Childrens, Middle Grade, Classics'),
('Classics, Fiction, Romance, Historical Fiction, Literature, Historical, Audiobook'),
('Classics, Nonfiction, History, Biography, Memoir, Historical, Holocaust'),
('Classics, Fiction, Dystopia, Fantasy, Politics, School, Literature');

SELECT row_number() over (order by Genres) R, Genres FROM mytable

Records: 5  Duplicates: 0  Warnings: 0




R
Genres


1
Classics, Fiction, Dystopia, Fantasy, Politics, School, Literature

2
Classics, Fiction, Historical Fiction, School, Literature, Young Adult, Historical

3
Classics, Fiction, Romance, Historical Fiction, Literature, Historical, Audiobook

4
Classics, Nonfiction, History, Biography, Memoir, Historical, Holocaust

5
Fantasy, Fiction, Young Adult, Magic, Childrens, Middle Grade, Classics


CREATE TABLE NewGenres
WITH RECURSIVE nrs as (
  SELECT 1 as a
  UNION ALL
  SELECT a+1 FROM nrs WHERE a<20)
,Genre as (
  SELECT R, a, trim(substring_index(substring_index(Genres,',',a),',',-1)) as Genre
  FROM nrs
  CROSS JOIN (SELECT row_number() over (order by Genres) R, Genres FROM mytable)  g
  WHERE a <= LENGTH(Genres)-LENGTH(REPLACE(Genres,',',''))+1
)
SELECT * 
FROM Genre
ORDER BY R,a;

SELECT * FROM NewGenres;

Records: 35  Duplicates: 0  Warnings: 0




R
a
Genre


1
1
Classics

1
2
Fiction

1
3
Dystopia

1
4
Fantasy

1
5
Politics

1
6
School

1
7
Literature

2
1
Classics

2
2
Fiction

2
3
Historical Fiction

2
4
School

2
5
Literature

2
6
Young Adult

2
7
Historical

3
1
Classics

3
2
Fiction

3
3
Romance

3
4
Historical Fiction

3
5
Literature

3
6
Historical

3
7
Audiobook

4
1
Classics

4
2
Nonfiction

4
3
History

4
4
Biography

4
5
Memoir

4
6
Historical

4
7
Holocaust

5
1
Fantasy

5
2
Fiction

5
3
Young Adult

5
4
Magic

5
5
Childrens

5
6
Middle Grade

5
7
Classics


SELECT 
    R, 
    sum(CASE WHEN Genre in ('Classics', 'Fiction', 'Literature', 'Young Adult', 'Fantasy') then 1 else 0 end) as NrofGenresInList,
    count(*) NumberOfGenres,
    GROUP_CONCAT(Genre ORDER BY Genre)  as ListOfGenres
FROM NewGenres
GROUP BY R

;


R
NrofGenresInList
NumberOfGenres
ListOfGenres


1
4
7
Classics,Dystopia,Fantasy,Fiction,Literature,Politics,School

2
4
7
Classics,Fiction,Historical,Historical Fiction,Literature,School,Young Adult

3
3
7
Audiobook,Classics,Fiction,Historical,Historical Fiction,Literature,Romance

4
1
7
Biography,Classics,Historical,History,Holocaust,Memoir,Nonfiction

5
4
7
Childrens,Classics,Fantasy,Fiction,Magic,Middle Grade,Young Adult

R	Genres
1	Classics, Fiction, Dystopia, Fantasy, Politics, School, Literature
2	Classics, Fiction, Historical Fiction, School, Literature, Young Adult, Historical
3	Classics, Fiction, Romance, Historical Fiction, Literature, Historical, Audiobook
4	Classics, Nonfiction, History, Biography, Memoir, Historical, Holocaust
5	Fantasy, Fiction, Young Adult, Magic, Childrens, Middle Grade, Classics

R	a	Genre
1	1	Classics
1	2	Fiction
1	3	Dystopia
1	4	Fantasy
1	5	Politics
1	6	School
1	7	Literature
2	1	Classics
2	2	Fiction
2	3	Historical Fiction
2	4	School
2	5	Literature
2	6	Young Adult
2	7	Historical
3	1	Classics
3	2	Fiction
3	3	Romance
3	4	Historical Fiction
3	5	Literature
3	6	Historical
3	7	Audiobook
4	1	Classics
4	2	Nonfiction
4	3	History
4	4	Biography
4	5	Memoir
4	6	Historical
4	7	Holocaust
5	1	Fantasy
5	2	Fiction
5	3	Young Adult
5	4	Magic
5	5	Childrens
5	6	Middle Grade
5	7	Classics

R	NrofGenresInList	NumberOfGenres	ListOfGenres
1	4	7	Classics,Dystopia,Fantasy,Fiction,Literature,Politics,School
2	4	7	Classics,Fiction,Historical,Historical Fiction,Literature,School,Young Adult
3	3	7	Audiobook,Classics,Fiction,Historical,Historical Fiction,Literature,Romance
4	1	7	Biography,Classics,Historical,History,Holocaust,Memoir,Nonfiction
5	4	7	Childrens,Classics,Fantasy,Fiction,Magic,Middle Grade,Young Adult