title: Merging rows in my pivot table query
body: <p>The below SQL query is supposed to show a teacher availability. There are 4 possible booking types - AM, PM, Full Day and Hourly. If there's an AM booking, the text in the cell should show PM, if there's a PM booking, it should show AM, if there's a full day booking, or both an AM and a PM booking, it should show 'xxx'. This all works fine.</p>
  
  <p>Now, I move on to the hourly bookings. It is safe to assume there is only ever going to be 2 hourly bookings for each day, 1 for the AM (start time &lt;= 12pm) and 1 for PM (end time > 12pm), meaning we should show 'xxx'. However I am really struggling to get this to show.</p>
  
  <pre><code>WITH Bookings AS
  (   SELECT  TeacherID,
              [WeekDay] = DATENAME(WEEKDAY, BookingDate),
              [Status] = CASE 
                              WHEN [3] &gt; 1 THEN 'XXX'
                              WHEN ([0] &gt; 0 AND [1] &gt; 0) THEN 'XXX'
                              WHEN [2] &gt; 0 THEN 'XXX'                            
                              WHEN [0] &gt; 0 THEN 'PM'
                              WHEN [1] &gt; 0 THEN 'AM'
                              WHEN [3] &gt; 0 AND CONVERT(time(0), EndTime) &lt;= CONVERT(time(0), '12:00:00') THEN 'PM'
                              WHEN [3] &gt; 0 AND CONVERT(time(0), StartTime) &gt;= CONVERT(time(0), '12:00:00') THEN 'AM'
                              WHEN [3] &gt; 0 AND CONVERT(time(0), StartTime) &lt;= CONVERT(time(0), '12:00:00') AND CONVERT(time(0), EndTime) &gt;= CONVERT(time(0), '12:00:00') THEN 'XXX'                           
                          END
      FROM    (   SELECT  TeacherID, BookingDate, BookingDuration, StartTime, EndTime, [x] = 1
                  FROM    BookingDays where (Status = 0 or Status IS NULL)
              ) BookingDays
              PIVOT
              (   SUM(x)
                  FOR BookingDuration IN ([0], [1], [2], [3])
              ) pvt
  
               WHERE BookingDate &gt;= DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 0) AND BookingDate &lt;= DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 6)
  
  ), PivotedBookings AS
  (   SELECT  *
      FROM    Bookings
              PIVOT
              (   MAX([Status])
                  FOR [WeekDay] IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])
              ) pvt
  
  )
  SELECT ID,Firstname,Surname,Band,'£' + CONVERT(varchar(50),DefaultChargeRateDaily) + '/' + '£' + CONVERT(varchar(50), DefaultPayRateDaily) as 'BandRates',Telephone,Mobile,Teacher,TeacherAssistant,KeyStage,MAX(Monday) Monday,MAX(Tuesday) Tuesday,MAX(Wednesday) Wednesday,MAX(Thursday) Thursday,MAX(Friday) Friday, Notes
    FROM (
  SELECT  t.ID, 
          t.Firstname, 
          t.Surname, 
          tb.Band,
          t.DefaultChargeRateDaily,
          t.DefaultPayRateDaily,
          t.Telephone,
          t.Mobile,
          t.Teacher,
          t.TeacherAssistant,
          CASE WHEN t.Nursery &gt; 0 THEN 'NUR' WHEN t.Reception &gt; 0 THEN 'REC' WHEN t.Year1 &gt; 0 THEN 'Y1' WHEN t.Year2 &gt; 0 THEN 'Y2' WHEN t.Year3 &gt; 0 THEN 'Y3' WHEN t.Year4 &gt; 0 THEN 'Y4' WHEN t.Year5 &gt; 0 THEN 'Y5' WHEN t.Year6 &gt; 0 THEN 'Y6' WHEN t.Year7 &gt; 0 THEN 'Y7' WHEN t.Year8 &gt; 0 THEN 'Y8' WHEN t.Year9 &gt; 0 THEN 'Y9' WHEN t.Year10 &gt; 0 THEN 'Y10' WHEN t.Year11 &gt; 0 THEN 'Y11' WHEN t.ALevel &gt; 0 THEN 'ALevel' END + ' - ' + CASE WHEN t.ALevel &gt; 0 THEN 'ALevel' WHEN t.Year11 &gt; 0 THEN 'Y11' WHEN t.Year10 &gt; 0 THEN 'Y10' WHEN t.Year9 &gt; 0 THEN 'Y9' WHEN t.Year8 &gt; 0 THEN 'Y7' WHEN t.Year6 &gt; 0 THEN 'Y6' WHEN t.Year5 &gt; 0 THEN 'Y6' WHEN t.Year4 &gt; 0 THEN 'Y4' WHEN t.Year3 &gt; 0 THEN 'Y3' WHEN t.Year2 &gt; 0 THEN 'Y2' WHEN t.Year1 &gt; 0 THEN 'Y1' WHEN t.Reception &gt; 0 THEN 'REC' WHEN t.Nursery &gt; 0 THEN 'NUR' ELSE '' END as 'KeyStage',
  
          Monday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 0) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Monday, '') END,
          Tuesday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 1) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Tuesday, '') END,
          Wednesday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 2) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Wednesday, '') END,
          Thursday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 3) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Thursday, '') END,
          Friday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 4) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Friday, '') END,
          Notes
  FROM    Teachers t
  
          LEFT JOIN PivotedBookings pb
              ON pb.TeacherID = t.ID
          LEFT JOIN TeacherBands tb
              ON tb.ID = t.Band
          LEFT JOIN AvailabilityNotes an 
              ON t.ID = an.TeacherID
              WHERE t.Active = 0 and (t.Status = 1 or t.Status = 0) and t.PrimarySchool = 1
              ) T1
   GROUP BY ID,Firstname,Surname,Telephone,Mobile,Teacher,TeacherAssistant,KeyStage,Notes,DefaultChargeRateDaily,DefaultPayRateDaily,Band
   ORDER BY Surname,Firstname asc
  </code></pre>
  
  <p>The first part generates the following - </p>
  
  <pre><code>SELECT  TeacherID,
              [WeekDay] = DATENAME(WEEKDAY, BookingDate),
              [Status] = CASE 
                              WHEN [3] &gt; 1 THEN 'XXX'
                              WHEN ([0] &gt; 0 AND [1] &gt; 0) THEN 'XXX'
                              WHEN [2] &gt; 0 THEN 'XXX'                            
                              WHEN [0] &gt; 0 THEN 'PM'
                              WHEN [1] &gt; 0 THEN 'AM'
                              WHEN [3] &gt; 0 AND CONVERT(time(0), EndTime) &lt;= CONVERT(time(0), '12:00:00') THEN 'PM'
                              WHEN [3] &gt; 0 AND CONVERT(time(0), StartTime) &gt;= CONVERT(time(0), '12:00:00') THEN 'AM'
                              WHEN [3] &gt; 0 AND CONVERT(time(0), StartTime) &lt;= CONVERT(time(0), '12:00:00') AND CONVERT(time(0), EndTime) &gt;= CONVERT(time(0), '12:00:00') THEN 'XXX'                           
                          END
      FROM    (   SELECT  TeacherID, BookingDate, BookingDuration, StartTime, EndTime, [x] = 1
                  FROM    BookingDays where (Status = 0 or Status IS NULL)
              ) BookingDays
              PIVOT
              (   SUM(x)
                  FOR BookingDuration IN ([0], [1], [2], [3])
              ) pvt
  
               WHERE BookingDate &gt;= DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 0) AND BookingDate &lt;= DATEADD(ww, DATEDIFF(ww,0,'04/22/2013'), 6)
  </code></pre>
  
  <p>Table columns:</p>
  
  <pre><code>TeacherID | WeekDay   | Status
  9386    Monday      PM
  9386    Tuesday     AM
  9386    Wednesday   XXX
  9763    Monday      PM
  9763    Tuesday     AM
  9763    Wednesday   XXX
  9927    Monday      PM
  9927    Tuesday     AM
  9927    Wednesday   XXX
  10358   Monday      PM
  10358   Monday      AM
  </code></pre>
  
  <p>We can see here, the last 2 rows need merging and showing in the status column as XXX.</p>
  
  <p>An example is shown in the screenshot below. Highlighted yellow, it is showing PM. However, there is 2 hourly bookings (BookingDuration ID 3), one 10:00am - 11:00am and another 14:00 - 15:00. THerefore, this should show XXX, rather than PM/AM. </p>
  
  <p><img src="https://i.stack.imgur.com/Y4zih.png" alt="Screenshot"></p>
  
  <p>I hope this makes sense!</p>