SQLTeam.com | Weblogs | Forums

Using 'FOR XML' to transpose / concatenate data


#1

I need to produce a list of staff and their start / end times for each day. The challenge (for me) is the format needed - shown below. I have seem examples of the 'FOR XML' clause in use and it looks like it could do the trick but I am struggling to understand how to build the query. Can anyone help?

DECLARE @Staff TABLE (stStaffID INT, StaffName VARCHAR(10))

INSERT INTO @Staff SELECT 1,'Bob'
INSERT INTO @Staff SELECT 2,'Fred'
INSERT INTO @Staff SELECT 3,'Mark'

DECLARE @Shifts TABLE (shStaffID INT, shShiftDate DATETIME, shStartTime CHAR(5), shEndTime CHAR(5))
 
INSERT INTO @Shifts SELECT 1,'2015/03/01','06:00','13:00'
INSERT INTO @Shifts SELECT 1,'2015/03/01','22:00','00:00'
INSERT INTO @Shifts SELECT 2,'2015/03/10','09:00','10:00'
INSERT INTO @Shifts SELECT 2,'2015/03/14','09:00','10:00'
INSERT INTO @Shifts SELECT 3,'2015/01/10','09:00','15:00'
INSERT INTO @Shifts SELECT 3,'2015/01/10','19:00','22:00'

-- Result set required as below (grouped by staff / date)

--	Name	Date	      Details
--	Bob     01/03/2015    Start: 06:00 End: 13:00, Start: 22:00 End: 00:00
--	Fred    01/03/2015    Start: 09:00 End: 10:00
--	Fred	14/03/2015    Start: 09:00 End: 10:00
--      Mark	10/01/2015    Start: 09:00 End: 15:00, Start: 19:00 End: 22:00

#2

Something like this:

with cte as
(
select *
from @Staff st
join @Shifts sh
  on st.stStaffID = sh.shStaffID
 )
 
select StaffName as Name, shShiftDate as Date, stuff(shifts,1,2,'') Details
from cte c1
cross apply
(
    select ',' + 
           ' Start: ' + shStartTime +
           ' End: '   + shEndTime
    from cte c2
    where c1.shStaffID = c2.shStaffID
      and c1.shShiftDate = c2.shShiftDate
    order by shStartTime
    for XML path('')
) _(shifts)