Oracle Database has a very simple way to aggregate multiple rows into one row. It can be done easily using a listagg function. When I started to work with Microsoft SQL, I was surprised that it has no direct alternative to Oracle listagg function. SQL Server developers recommend using FOR XML PATH expression to get similar results (like here). This work really good despite it is not so easy to use. Because this operation is not so popular among SQL Server developers, I would like to share a task which I was solving this week. I believe somebody could find it useful.
This was the task definition: I have a table with employees’ holiday terms. Each term has a holiday start and holiday end dates. I have a table with a list of all public holidays in the Czech Republic. I needed to list all public holidays during each holiday term. Because there can be multiple public holidays during each request I needed to concatenate multiple days into one row with a separator. Finally, I decided to write a function which returns all public holidays from a specific interval as a varchar. This is the code of the function.
CREATE FUNCTION [dbo].[Duflistpublicholidaysininterval] (@intervalStart AS DATE, @intervalEnd AS DATE) returns VARCHAR(200) AS BEGIN DECLARE @datesInInterval AS VARCHAR(max) SELECT @datesInInterval = Stuff((SELECT '; ' + Format([date], 'd. M. yyyy') FROM [dbo].[view_publicholidays] WHERE [date] >= @intervalStart AND [date] <= @intervalEnd FOR xml path('')), 1, 1, '') RETURN @datesInInterval END
And this is the final SELECT query.
SELECT absencestartdate, abcenseenddate, Duflistpublicholidaysininterval (ab.startdate, ab.enddate) AS PublicHolidaysInInterval FROM [dbo].[absence] ab
This is and example of the output.