Severus Tux Severus Tux - 21 days ago 6
SQL Question

Concatinate unknown number of values in sqlite

I am using sqlite 3.15.1 .

I have a table

master
containing master timetable of a college.

It looks like :

day sem sec hour sub_id
---------- ---------- ---------- ---------- ----------
MON 5 B 4 10IS51
MON 5 B 4 10IS53
MON 5 B 5 10CS54
MON 5 B 6 10CS55
MON 5 B 7 10CS53
MON 3 A 1 10CS33


and many more values....

There are multiple sub_id for same other values , meaning - On Monday 1st hour, 5th B students might have 2 or more lab (sub_id). (Its conducted in batches).

To get a proper timetable, I am doing this :

select day,
max( case when hour =1 then sub_id end ) as 'hour-1',
max( case when hour =2 then sub_id end ) as 'hour-2',
max( case when hour =3 then sub_id end ) as 'hour-3',
max( case when hour =4 then sub_id end ) as 'hour-4',
max( case when hour =5 then sub_id end ) as 'hour-5',
max( case when hour =6 then sub_id end ) as 'hour-6',
max( case when hour =7 then sub_id end ) as 'hour-7',
max( case when hour =8 then sub_id end ) as 'hour-8'
from master
where sem=5 and sec='B'
group by day
order by day;


But it gives only one value when multiple values occur i.e, the
max()
value. When I use
min()
, I get the min() value. How can I get both ?

the resultant view looks like :

day hour-1 hour-2 hour-3 hour-4 hour-5 hour-6 hour-7 hour-8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
FRI 10CS52 10CS54 10CS53 10CS55 HRD HRD TUT
MON 10CSL58 10CSL58 10CSL58 10IS51 10CS54 10CS55 10CS53
SAT 10IS51 10CS55 10CS56 10CS52
THU 10CS53 10IS51 10CS54 10CS52
TUE 10CS54 10CS52 10CS56 10CS56
WED 10CS56 10IS51 10CS53 10CS55 CSA CSA CSA


But I want something like this :

day hour-1 hour-2 hour-3 hour-4 hour-5 hour-6 hour-7 hour-8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
FRI 10CS52,10CS53 10CS54 10CS53 10CS55 HRD HRD TUT
MON 10CSL58 10CSL58,10CSL33 10CSL58 10IS51 10CS54 10CS55 10CS53
SAT 10IS51,10IS48 10CS55 10CS56 10CS52
THU 10CS53 10IS51 10CS54 10CS52
TUE 10CS54 10CS52 10CS56 10CS56
WED 10CS56 10IS51 10CS53 10CS55 CSA CSA CSA


That is, All the classes - comma separated , instead of min() or max().

Is it possible to achieve this ? please help me.

Thanks.

Answer

Replace MIN/MAX with GROUP_CONCAT

select day,
group_concat( case when hour =1 then sub_id end ) as 'hour-1',
group_concat( case when hour =2 then sub_id end ) as 'hour-2',
group_concat( case when hour =3 then sub_id end ) as 'hour-3',
group_concat( case when hour =4 then sub_id end ) as 'hour-4',
group_concat( case when hour =5 then sub_id end ) as 'hour-5',
group_concat( case when hour =6 then sub_id end ) as 'hour-6',
group_concat( case when hour =7 then sub_id end ) as 'hour-7',
group_concat( case when hour =8 then sub_id end ) as 'hour-8'
from master
where sem=5 and sec='B'
group by day
order by day;