Two results in a row with two columns and group

I am trying to mix multiple results on a single line

Here the data example:

TOPICS
    topicID   state    title
    1           A     'Hello'
    1           B     'Bye'
    2           A     'Great'

And mixed with:

STUDENTS
        topicID   studentID
        1            23
        1            27
        2            33
        2            40

I want this result:

Topic     A         B      Students
1      'Hello'    'Bye'     23,27
2      'Great'     null     33,40

Now I correctly receive users using GROUP_CONCAT(DISTINCT studentID SEPARATOR ‘,’) in SELECT but i can’t use it again. I have tried CASE but have not succeeded.

Answer

You can use conditional aggregation and a correlated subquery:

select t.topicid,
       max(case when t.state = 'A' then t.title end) as a,
       max(case when t.state = 'B' then t.title end) as b,
       (select group_concat(s.studentid)
        from students s
        where s.topicid = t.topicid
       ) as students
from topics t
group by topicid;

The correlated subquery eliminates the need for group_concat(distinct), which is more expensive than just group_concat().