CODEWITHSUNDEEP
sqlsqliteconcatenation
Severus Tux
Severus Tux

Reputation: 285

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.

Upvotes: 2

Views: 67

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

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;

Upvotes: 2

Related Questions