CODEWITHSUNDEEP
sqlmysqldatabasedatabase-design
Sam
Sam

Reputation: 8693

Need help in formulating the query output in a certain fashion

We have a table which contains TV channel listings.

create table listing (id, channel_name, event_name, event_time)

a) listing (1, "ABC Channel", "All My Children", 6.00 AM)
b) listing (2, "ABC Channel", "News", 7 AM)
c) listing (3, "ABC Channel", "One life to live", 7.15 AM)
d) listing (4, "ABC Channel", "General Hospital", 8.00 AM)
e) listing (5, "ABC Channel", "News", 9.00 AM)
f) listing (6, "ABC Channel", "Movie 1", 9.15 AM)
g) listing (7, "ABC Channel", "News", 10.00 AM)

My web page shows a particular tv channel listing as follows:-

event_name        event_times
---------------------------------------------
All My Children   6.00 AM
News              7.00 AM, 9.00 AM, 10.00 AM
One life to live  7.15 AM
General Hospital  8.00 AM
Movie 1           9.15 AM

All News timings are consolidate into a single entry, currently I am consolidating the entries at the application layer, is it possible to do this at the query level.

Upvotes: 0

Views: 46

Answers (2)

Alec
Alec

Reputation: 9078

Try something like this:

  SELECT event_name,
         GROUP_CONCAT(event_time SEPARATOR ', ' ORDER BY event_time) AS event_times
    FROM listing
GROUP BY event_name
ORDER BY event_name

If you need it for separate channels, this should work:

  SELECT event_name,
         GROUP_CONCAT(event_time SEPARATOR ', ' ORDER BY event_time) AS event_times
    FROM listing
   WHERE channel_name = 'ABC Channel'
GROUP BY event_name
ORDER BY event_name

Upvotes: 3

p.campbell
p.campbell

Reputation: 100607

Perhaps UNION the non-News along with the News rows. Only the News needs its times concatenated.

SELECT event_name,
       event_times
FROM  (
  SELECT
    event_name, event_time
  FROM listing
  WHERE event_name != 'News' 
  UNION
  SELECT event_name, GROUP_CONCAT(event_time SEPARATOR ', ') event_time
  FROM listing
  WHERE event_name = 'News' 
  ) AS All 
ORDER BY event_times ASC

Upvotes: 1

Related Questions