Loading specific dates from a given interval into one line

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)
      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

And this is the final SELECT query.

SELECT absencestartdate,
       Duflistpublicholidaysininterval (ab.startdate, ab.enddate) AS
FROM   [dbo].[absence] ab

This is and example of the output.


Leave a Reply

Name and email address are required. Your email address will not be published.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: