Aggregating strings in SQL Server, using Irish Saints Days

I nowadays enjoy working with front-end developers using JavaScript, especially now that SQL Server has the tools that allow the SQL Developer to accommodate their requirements. There is less tension now, because if the SQL Server developer can deliver exactly what they need, then there is less temptation to allow users access to base tables, and less of an urge for the full-stack developer to ask for it. You can now have a nice simple RESTful interface that provides everything that the application requires via a set of functions.

What has brought about this change? The introduction of String_agg() has made a lot of difference. So, of course, has the JSON support for reading and writing JSON. SQL Server now is a great deal more ‘sympatico’ with the world of web-based development and microservices

You now have a lot more opportunity to deliver to the application precisely what is needed for a display without so much tedious data-pummeling by the front-end developer. I ought to illustrate this a bit from the murky depths of SQL Server.

Let’s choose as sample for our calendar-based information, a list of Irish saints days. The code to create this can be downloaded from here.

There are 128 Irish saints whose saints days are known, of the 190 who are still revered. There are 10,000 in all, but that is discussed elsewhere.

In the actual application this was derived from, the report was a calendar-based list of appointments. You might have all sorts of calendar-based reports that could use this sort of visualisation. You can use anything that has an event followed by a date.

This routine gives you a list of relevant saints for each day in which one or more saint has a day.

Which will give the following table (I used MS Word to display it though it usually ends up in an HTML table. As you see, one or more saint is listed on the days that have a saints day.

Sometimes, you need to concatenate strings in a particular order. You could do it in the bad old days using the XML trick. String-agg has a syntax for it . You’ll see that I use the feature built into String-Agg().

T-SQL requires the use of the WITHIN GROUP clause when ordering the result set. This is more complicated than the PostgreSQL String_agg() function and MySQL’s GROUP_CONCAT() function which both make do with just an ORDER BY clause. String_agg(), unlike group_concat(), doesn’t have a DISTINCT option, but at the moment I can’t think of a use for this.

Of course, you might be asked to do a scrolling year view like this…

Which you could do more simply like this.

You’ll notice here that you can’t use string_agg() in the same way as you might a SUM(), because of the concatenation delimiter that you specify. You’d end up with lots of empty delimiters. Instead, you need to aggregate the saints for the day separately beforehand, and use something neutral like MIN() or MAX(). It doesn’t matter because it has already been aggregated so there will be only one of each.

You can get exactly the same result this way, using JSON; it has added logic to make sure that even if not every monthday (1-31) or month (1-12) is represented, it will still work.


Of course, there is always going to be someone who wants the report in this format:


So here is the code to provide all the saints days for the month of whatever date you place into the variable @date, or the current date if you put a null in it.


I’m not advocating that you get stuck into calendar displays of Irish Saints, worthy though they are. I would like to make a point about the use of calendar-style reports. I once had a boss who was very keen indeed on having his financial reports based in a calendar form. He could see instantly if revenues tailed off on a Friday and could zoom in on the consequences of various IT problems or off-site team-bonding sessions on revenues. He could pick out all sorts of details that a bare list wouldn’t tell him about. It is also great for alerts and warnings in IT processes because the patterns are more readily apparent. Our brains are used to calendars, and the brains of managers will benefit from any help you can give them.

I also want to make the point that the simple SQL Reports you dish out may look splendid, but are they exactly right for communicating the particular type detailed financial or quantitative information you are being asked for?

The build script for The Irish Saints Day table is here