Okay, so there isn't really any great way to do this. SQL Server doesn't have a text concatenation aggregate function.
Here are some possible methods:
1. Put the raw query of Employees and their leave periods into a temp table. Put the distinct list of employees into a second temp table with a blank leave description column, then loop through the first temp table to update the descriptions in the second. This would be one loop per max number of leave periods per employee.
2. Use the same temp tables as the first, but loop through each employee and build the leave description for each one and update it in the second table.
3. Create a UDF that builds the leave description for each employee and call it once per row.
4. Use the manual pivot technique with enough expressions to cover the greatest possible number of leave periods. In some ways, this is better than all the others, but it also suffers from being very big and unwieldy, and risks losing data if the number of leave periods is unexpectedly high some time.
(Of course, all of these risk losing data if the description exceeds the column limit. Using varchar(8000) is probably enough, but, maybe not. So varchar(max) can come into play but performance can be lower with this.)
5. Create a CLR assembly in .Net that performs concatenation and use it in one of several ways. There are articles on this. It seems cool, but I don't recommend it unless your database is already using a lot of CLR. You wouldn't want to get into it just for one situation like this.
6. Leave everything in rows but make the employee name blank when it's not the first row. This can give the appearance of having everything grouped without actually being that way.
I'm going to show you #3 and #6. #3 is loop-based and so it ultimately sucks, but it works and is easy to show.
Code:
-- UDF
GO
CREATE FUNCTION dbo._usp_report_FTE_Employee_LeaveDescription (
@EmployeeID int,
@BeginPeriod datetime, -- must be the 1st of the month beginning the period, at exactly midnight
@EndPeriod datetime -- must be the 1st of the month after the desired period, at exactly midnight
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @LeaveDescription varchar(8000)
SELECT @LeaveDescription =
Coalesce(@LeaveDescription + '; ', '')
+ L.LeaveTypeID + ': ' + CONVERT(varchar(30), L.LeaveBeginDate, 107)
+ ' to ' + CONVERT(varchar(30), L.LeaveEndDate, 107)
+ ', ' + CASE WHEN L.IsPaidFlag IS NULL THEN 'Unpaid' ELSE 'Paid' END
FROM EmployeeLeave L
WHERE
L.EmployeeID = @EmployeeID
AND L.LeaveEndDate >= @BeginPeriod -- Leave ends after the desired period begins
AND L.LeaveBeginDate < @EndPeriod -- Leave begins before the desired period ends
RETURN @LeaveDescription
END
Code:
CREATE PROCEDURE dbo._usp_report_FTE_Leaves
@BeginPeriod datetime, -- pass in any date in the desired month
@EndPeriod datetime -- pass in any date in the desired month
AS
SET NOCOUNT ON
SET @BeginPeriod = DateAdd(mm, DateDiff(mm, 0, @BeginPeriod), 0)
SET @EndPeriod = DateAdd(mm, DateDiff(mm, 0, @EndPeriod) + 1, 0)
SELECT
E.EmployeeID,
EmployeeName = E.LastName + ', ' + E.FirstName,
E.Title,
LeaveDescription = dbo._usp_report_FTE_Employee_LeaveDescription(E.EmployeeID, @BeginPeriod, @EndPeriod)
FROM
Employees E
WHERE
EXISTS (
SELECT 1
FROM EmployeeLeave L
WHERE
E.EmployeeID = L.EmployeeID
AND L.LeaveEndDate >= @BeginPeriod -- Leave ends after the desired period begins
AND L.LeaveBeginDate < @EndPeriod -- Leave begins before the desired period ends
)
And now the "empty cells" version that only makes it LOOK like it's grouped. And you can order by EmployeeID, Selector to make sure the rows come out in the right order.
Code:
CREATE PROCEDURE dbo._usp_report_FTE_Leaves
AS
SET NOCOUNT ON -- this should be standard in your SPs
@BeginPeriod datetime, -- pass in any date in the desired month
@EndPeriod datetime -- pass in any date in the desired month
AS
SET @BeginPeriod = '20091005'
SET @EndPeriod = '20091105'
SELECT
Selector = Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate),
E.EmployeeID,
EmployeeName = CASE WHEN Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate) = 1 THEN E.LastName + ', ' + E.FirstName ELSE NULL END,
Title = CASE WHEN Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate) = 1 THEN E.Title ELSE NULL END,
LeaveDescription = Convert(varchar(11), Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate))
+ '. ' + L.LeaveTypeID + ': ' + CONVERT(varchar(30), L.LeaveBeginDate, 107)
+ ' to ' + CONVERT(varchar(30), L.LeaveEndDate, 107)
+ ', ' + CASE WHEN L.IsPaidFlag IS NULL THEN 'Unpaid' ELSE 'Paid' END
FROM
Employees E
INNER JOIN EmployeeLeave L ON E.EmployeeID = L.EmployeeID
WHERE
L.LeaveEndDate >= DateAdd(mm, DateDiff(mm, 0, @BeginPeriod), 0) -- Leave ends after the desired period begins
AND L.LeaveBeginDate < DateAdd(mm, DateDiff(mm, 0, @EndPeriod) + 1, 0) -- Leave begins before the desired period end
I have to say, this solution is pretty slick.
I tested these and they work. You'll have to put your db names back in there.
Erik
P.S. You should never use a data type declaration without a length for those data types that accept a length. Specifically, Convert(varchar, expression) is not best practice. It will bite you some day. You must always specify a length as in Convert(varchar(30), expression).
