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:

Back to the twelve days of Christmas, first create a table to contain the gifts:

CREATE TABLE #christmas_gifts (day_num INT, gift VARCHAR(30))

And insert the data:

INSERT INTO #christmas_gifts VALUES (1, 'a Partridge in a Pear Tree')
INSERT INTO #christmas_gifts VALUES (2, '2 Turtle Doves')
INSERT INTO #christmas_gifts VALUES (3, '3 French Hens')
INSERT INTO #christmas_gifts VALUES (4, '4 Calling Birds')
INSERT INTO #christmas_gifts VALUES (5, '5 Gold Rings')
INSERT INTO #christmas_gifts VALUES (6, '6 Geese-a-Laying')
INSERT INTO #christmas_gifts VALUES (7, '7 Swans-a-Swimming')
INSERT INTO #christmas_gifts VALUES (8, '8 Maids-a-Milking')
INSERT INTO #christmas_gifts VALUES (9, '9 Ladies Dancing')
INSERT INTO #christmas_gifts VALUES (10, '10 Lords-a-Leaping')
INSERT INTO #christmas_gifts VALUES (11, '11 Pipers Piping')
INSERT INTO #christmas_gifts VALUES (12, '12 Drummers Drumming')

And here is the query to return each verse of the song as a single row:

SELECT
 'On the '
 + CAST(theday.day_num AS VARCHAR)
 + CASE 
   WHEN theday.day_num%10=1 AND theday.day_num%100<>11 THEN 'st'
   WHEN theday.day_num%10=2 AND theday.day_num%100<>12 THEN 'nd'
   WHEN theday.day_num%10=3 AND theday.day_num%100<>13 THEN 'rd'
   ELSE 'th' 
   END
 + ' day of Christmas SQL gave to me '
 + REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
    (SELECT todate.gift
     FROM #christmas_gifts todate 
     WHERE todate.day_num <= theday.day_num
     ORDER BY todate.day_num DESC
     FOR XML PATH('')),
    '</gift><gift>a ', ' and a '),
    '</gift><gift>', ', '),
    '</gift>', ''),
    '<gift>', '') AS verse
FROM #christmas_gifts theday

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:

SELECT todate.gift
FROM #christmas_gifts todate 
WHERE todate.day_num <= 2
ORDER BY todate.day_num DESC
FOR XML PATH('')

The XML you should get back is:

<gift>2 Turtle Doves</gift><gift>a Partridge in a Pear Tree</gift>

You may spot that this isn’t well formed XML, because there is no containing element (such as <gifts>…</gifts>). 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).