ASP.NET

Merge rows in SELECT query RSS

4 replies

Last post Dec 21, 2012 12:50 PM by KiaranNafade

‹ Previous Thread|Next Thread ›

- Reply
mbaughan

Member

14 Points

45 Posts
Merge rows in SELECT query

Dec 21, 2012 12:14 PM|LINK
I have the following code -
```
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
```
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.
Matt

Systems Engineer / ASP.NET Web Developer

mbaughan@raecomputing.com
- Reply
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:
```
GROUP BY TeacherID, Firstname, Surname, bd.BookingDuration
```
Hope this helps.
www.imoveisemexposicao.com.br
- Reply
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!

Matt

Systems Engineer / ASP.NET Web Developer

mbaughan@raecomputing.com
- Edited by mbaughan on Dec 21, 2012 12:34 PM
- Edited by mbaughan on Dec 21, 2012 12:35 PM
- Reply
silvioyf

Participant

1754 Points

340 Posts
Re: Merge rows in SELECT query

Dec 21, 2012 12:42 PM|LINK
Try:
```
SELECT t.Firstname, t.Surname,
MIN(CASE WHEN bd.DayText = 'Monday' THEN bds.Description ELSE NULL END) "Monday",
-- ...
```
and
```
GROUP BY t.TeacherID, t.Firstname, t.Surname
```
Hope this helps.
www.imoveisemexposicao.com.br
- Marked as answer by mbaughan on Dec 21, 2012 12:52 PM
- Reply
KiaranNafade

Member

136 Points

59 Posts

Re: Merge rows in SELECT query

Dec 21, 2012 12:50 PM|LINK

use Union Function