Luis Moran Luis Moran - 7 months ago 10
SQL Question

Multiple queries in one query?

I have a table in a database with start and stop times for each day of the week for some staff in Mysql in the following way:


Agent Day Start End
agent1 Mon 17:00 23:00
agent1 Tue 16:00 23:00
agent1 Wed 18:00 23:00


I need this to the displayed in the following way:


Agent Mon Tue Wed
start stop start stop start stop
agent1 17:00 23:00 16:00 23:00 18:00 23:00


Is there a way to do this through a query? I was thinking of doing multiple queries in one query but I don't quite get it.

Answer

You can sue a lot of case

select agent,
      case day when 'Mon' then start end as start_mon,
      case day when 'Mon' then `end` end as end_mon, 
      case day when 'Tue' then start end as start_tue,
      case day when 'Tue' then `end` end as end_the,    
      case day when 'Wed' then start end as start_wed,
      case day when 'Wed' then `end` end as end_wed
 from your_table      

for gettin the value on the same row of agent you can use a temp table and group bt

select agent, max(start_mon), max(end_mon), max(start_tue), max(end_tue), max(start_wed), max(end_web)
   from ( 
        select agent,
           case day when 'Mon' then start end as start_mon,
           case day when 'Mon' then `end` end as end_mon, 
           case day when 'Tue' then start end as start_tue,
           case day when 'Tue' then `end` end as end_the,    
           case day when 'Wed' then start end as start_wed,
           case day when 'Wed' then `end` end as end_wed
        from your_table ) as t
    group by agent;