SELECT t.Firstname, t.Surname, bd.BookingDuration,
MIN(CASE WHEN bd.DayText = 'Monday' THEN bds.Description ELSE NULL END) "Monday",
MIN(CASE WHEN bd.DayText = 'Tuesday' THEN bds.Description ELSE NULL END) "Tuesday",
MIN(CASE WHEN bd.DayText = 'Wednesday' THEN bds.Description ELSE NULL END) "Wednesday",
MIN(CASE WHEN bd.DayText = 'Thursday' THEN bds.Description ELSE NULL END) "Thursday",
MIN(CASE WHEN bd.DayText = 'Friday' THEN bds.Description ELSE NULL END) "Friday"
FROM Teachers t
left join BookingDays bd on t.ID = bd.TeacherID
inner join BookingDurations bds on bd.BookingDuration = bds.ID
where BookingDate >= (SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)) and BookingDate <= (SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 6))
GROUP BY TeacherID, Firstname, Surname, DayText, bd.BookingDuration, bds.Description
ORDER BY BookingDuration ASC
mbaughan
Member
14 Points
45 Posts
Merge rows in SELECT query
Dec 21, 2012 12:14 PM|LINK
I have the following code -
Which returns a table -
Firstname | Surname | BookingDUration | Monday | Tuesday | Wednesday | Thursday | Friday
Tony Martin 2 FullDay NULL NULL NULL NULL
Tony Martin 2 NULL FullDay NULL NULL NULL
Charlotte Vale 2 FullDay NULL NULL NULL NULL
How can I modify this query to display -
Firstname | Surname | BookingDUration | Monday | Tuesday | Wednesday | Thursday | Friday
Tony Martin 2 FullDay FullDay NULL NULL NULL
Charlotte Vale 2 FullDay NULL NULL NULL NULL
Thanks.
Systems Engineer / ASP.NET Web Developer
mbaughan@raecomputing.com
silvioyf
Participant
1754 Points
340 Posts
Re: Merge rows in SELECT query
Dec 21, 2012 12:26 PM|LINK
Try changing the Group By clause:
Hope this helps.
mbaughan
Member
14 Points
45 Posts
Re: Merge rows in SELECT query
Dec 21, 2012 12:33 PM|LINK
That works, but i forgot to mention that the Booking Duration might not always be the same. There is FullDay, AM, PM and Hourly.
Monday Tuesday Wednesday Thursday Friday
Tony Martin 0 NULL AM NULL AM NULL
Tony Martin 1 NULL PM PM NULL PM
Tony Martin 2 FullDay NULL NULL NULL NULL
For example, if there is an AM and a PM booking on a Monday, I guess i need to merge the string values into one row so Monday: "AM / PM":
Monday Tuesday Wednesday Thursday Friday
Tony Martin FullDay AM/PM PM AM PM
Any ideas?
Thanks!
Systems Engineer / ASP.NET Web Developer
mbaughan@raecomputing.com
silvioyf
Participant
1754 Points
340 Posts
Re: Merge rows in SELECT query
Dec 21, 2012 12:42 PM|LINK
Try:
and
Hope this helps.
KiaranNafade
Member
136 Points
59 Posts
Re: Merge rows in SELECT query
Dec 21, 2012 12:50 PM|LINK
use Union Function