A festive post for the twelfth day of Christmas and one way to avoid taking the decorations down for a while longer!
If you have each gift from the “Twelve days of Christmas” song as an individual record in a SQL Server database table, can you write a query that returns one row for each verse of the song?
Yes you can. This problem is similar to other cases where you need to flatten multiple rows into a single string. In this case for each day we want a comma separated list of the gifts for that day and all preceding days.
Another case you may have come across is flattening multiple access rights per user into a single row per user, where each row has a comma separated list of that user’s rights:
| User | Access Rights |
|---|---|
| Anne | Read, Write |
| Bob | Read |
| Chad | Admin, Read, Write |
Back to the twelve days of Christmas, first create a table to contain the gifts:
And insert the data:
And here is the query to return each verse of the song as a single row:
The approach used above has been around for a while and a quick Google will turn up plenty of background material. The inner query selects the list of gifts for the day and its previous days as an XML structure (SELECT … WHERE todate.day_num <= theday.day_num … FOR XML). We then replace the XML tags with commas or “and”.
If you want to get a feel for the XML that is returned by the inner select then you can run that part of the query and take a look, e.g. if you run the inner select for day 2:
The XML you should get back is:
You may spot that this isn’t well formed XML, because there is no containing element (such as …). The absence of a containing element was specified with the empty string in the PATH(”) part of the FOR XML clause. Leaving out the containing element made the subsequent string replacements a bit simpler (arguably).