String_agg()<\/code> 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 \u2018sympatico\u2019 with the world of web-based development and microservices<\/p>\nYou 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.<\/p>\n
Let\u2019s choose as sample for our calendar-based information, a list of Irish saints days. The code to create this can be downloaded from here.<\/a><\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/06\/word-image.png\")
<\/p>\n
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<\/a>.<\/p>\nIn 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.<\/p>\n
This routine gives you a list of relevant saints for each day in which one or more saint has a day.<\/p>\n
\/* glue together all saints for a day *\/\r\nSELECT\r\n --the name of the month in the current language \r\n DateName(MONTH,DateAdd(MONTH,MonthNumber,N'2018-12-01T00:00:00')), \r\n\t--the list of Irish saints with saints-days in the month\r\n String_Agg(saints, '. ') WITHIN GROUP( ORDER BY MonthNumber ASC)\r\n --a simple derived table containing all the valid month numbers\r\n FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) \r\n AS Month ( MonthNumber )\r\n \/* cope with a month with no entries with a blank column *\/\r\n LEFT OUTER JOIN\r\n (\/* glue together all saints for a day *\/\r\n SELECT \r\n\t Coalesce(\r\n Convert(VARCHAR(2), DateOfMonth) --this is english abbreviation only. Sorry\r\n + Substring('stndrdthththththththththththththththththstndrdthththththththst',\r\n (DateOfMonth * 2) - 1, 2 ) + ': ' + String_Agg(NAME, ', ') \r\n\t\t , '' ) AS Saints, DateOfMonth, MonthNo\r\n FROM\r\n (\/* collect the basic information in a derived table *\/\r\n SELECT name, DatePart(DAY, saintsDay) AS DateOfMonth,\r\n DatePart(MONTH, saintsDay) AS MonthNo\r\n FROM #IrishSaintsDay\r\n WHERE saintsDay IS NOT NULL\r\n ) AS f(NAME, DateOfMonth, MonthNo)\r\n GROUP BY DateOfMonth, MonthNo\r\n ) AS g(Saints, DateOfMonth, MonthNo)\r\n ON g.MonthNo = Month.MonthNumber\r\n GROUP BY MonthNumber;<\/pre>\nWhich 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.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/06\/word-image-1.png\")
<\/p>\n
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\u2019ll see that I use the feature built into String-Agg().<\/p>\n
String_Agg(saints, '') WITHIN GROUP( ORDER BY MonthNumber ASC)<\/pre>\nT-SQL requires the use of the WITHIN GROUP<\/code> clause when ordering the result set. This is more complicated than the PostgreSQL String_agg()<\/code> function and MySQL\u2019s GROUP_CONCAT()<\/code> function which both make do with just an ORDER BY<\/code> clause. String_agg()<\/code>, unlike group_concat()<\/code>, doesn\u2019t have a DISTINCT<\/code> option, but at the moment I can\u2019t think of a use for this.<\/p>\nOf course, you might be asked to do a scrolling year view like this\u2026<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/06\/word-image-2.png\")
<\/p>\n
Which you could do more simply like this.<\/p>\n
SELECT Rownumber AS day,\r\n Max(CASE WHEN ColumnNumber = 1 THEN Saints ELSE '' END) AS January,\r\n Max(CASE WHEN ColumnNumber = 2 THEN Saints ELSE '' END) AS February,\r\n Max(CASE WHEN ColumnNumber = 3 THEN Saints ELSE '' END) AS March,\r\n Max(CASE WHEN ColumnNumber = 4 THEN Saints ELSE '' END) AS April,\r\n Max(CASE WHEN ColumnNumber = 5 THEN Saints ELSE '' END) AS May,\r\n Max(CASE WHEN ColumnNumber = 6 THEN Saints ELSE '' END) AS June,\r\n Max(CASE WHEN ColumnNumber = 7 THEN Saints ELSE '' END) AS July,\r\n Max(CASE WHEN ColumnNumber = 8 THEN Saints ELSE '' END) AS August,\r\n Max(CASE WHEN ColumnNumber = 9 THEN Saints ELSE '' END) AS September,\r\n Max(CASE WHEN ColumnNumber = 10 THEN Saints ELSE '' END) AS october,\r\n Max(CASE WHEN ColumnNumber = 11 THEN Saints ELSE '' END) AS November,\r\n Max(CASE WHEN ColumnNumber = 12 THEN Saints ELSE '' END) AS December\r\n FROM\r\n (\r\n SELECT String_Agg(NAME, ', ') AS Saints, RowNumber, ColumnNumber\r\n FROM\r\n (\r\n SELECT name, DatePart(DAY, saintsDay) AS RowNumber,\r\n DatePart(MONTH, saintsDay) AS ColumnNumber\r\n FROM #IrishSaintsDay\r\n WHERE saintsDay IS NOT NULL\r\n ) AS f(NAME, RowNumber, ColumnNumber)\r\n GROUP BY RowNumber, ColumnNumber\r\n ) AS g(Saints, Rownumber, ColumnNumber)\r\n GROUP BY Rownumber\r\n ORDER BY Rownumber;\r\n<\/pre>\nYou\u2019ll notice here that you can\u2019t use string_agg()<\/code> in the same way as you might a SUM()<\/code>, because of the concatenation delimiter that you specify. You\u2019d 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()<\/code> or MAX()<\/code>. It doesn\u2019t matter because it has already been aggregated so there will be only one of each.<\/p>\nYou 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.<\/p>\n
SELECT *\r\n FROM\r\n OpenJson(\r\n (\r\n SELECT '[' + String_Agg(TheJSONList, ',') WITHIN GROUP(\r\n ORDER BY DayNumber ASC) + ']'\r\n FROM\r\n (\r\n SELECT '[' + Convert(VARCHAR(2), DayNumber) + ',\"'\r\n + String_Agg(Coalesce(String_Escape(Saints, 'json'), ''), '\",\"') WITHIN GROUP(\r\n ORDER BY MonthNumber ASC) + '\"]' AS TheJSONList, DayNumber\r\n FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11),\r\n(12)\r\n) AS Month (MonthNumber)\r\n CROSS JOIN\r\n (\r\n VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11),\r\n (12), (13), (14), (15), (16), (17), (18), (19), (20), (21),\r\n (22), (23), (24), (25), (26), (27), (28), (29), (30), (31)\r\n ) AS Day (DayNumber)\r\n LEFT OUTER JOIN\r\n (\r\n SELECT String_Agg(NAME, ', ') AS Saints, RowNumber, ColumnNumber\r\n FROM\r\n (\r\n SELECT name, DatePart(DAY, saintsDay) AS RowNumber,\r\n DatePart(MONTH, saintsDay) AS ColumnNumber\r\n FROM #IrishSaintsDay\r\n WHERE saintsDay IS NOT NULL\r\n ) AS f(NAME, RowNumber, ColumnNumber)\r\n GROUP BY RowNumber, ColumnNumber\r\n ) AS g(Saints, Rownumber, ColumnNumber)\r\n ON g.Rownumber = Day.DayNumber\r\n AND g.ColumnNumber = Month.MonthNumber\r\n GROUP BY DayNumber\r\n ) AS lines(TheJSONList, DayNumber)\r\n )\r\n )\r\n WITH\r\n (\r\n Day INT '$[0]', Jan NVARCHAR(1000) '$[1]', Feb NVARCHAR(1000) '$[2]',\r\n Mar NVARCHAR(1000) '$[3]', Apr NVARCHAR(1000) '$[4]',\r\n May NVARCHAR(1000) '$[5]', Jun NVARCHAR(1000) '$[6]',\r\n Jul NVARCHAR(1000) '$[7]', Aug NVARCHAR(1000) '$[8]',\r\n Sep NVARCHAR(1000) '$[9]', Oct NVARCHAR(1000) '$[10]',\r\n Nov NVARCHAR(1000) '$[11]', Dec NVARCHAR(1000) '$[12]'\r\n );<\/pre>\n <\/p>\n
Of course, there is always going to be someone who wants the report in this format:<\/p>\n
![\"294-img73.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/06\/294-img73-jpg.jpeg\")
<\/p>\n
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.<\/p>\n
DECLARE @Date DATETIME = '01 Jun 2019';\r\n--nail down the start of the week\r\nDECLARE @MonthLength INT; --number of days in the month\r\nDECLARE @MonthStartDW INT; --the day of the week that the month starts on\r\nDECLARE @Month INT; --the month (1.10)\r\n--if no date is specified, then use the current date\r\nSELECT @Date =\r\n '01 ' + Substring(Convert(CHAR(11), Coalesce(@Date, GetDate()), 113), 4, 8);\r\n--get the number of days in the month and the day of the week that the month starts on\r\nSELECT @MonthLength =\r\n DateDiff( DAY, Convert(CHAR(11), @Date, 113),\r\n Convert(CHAR(11), DateAdd(MONTH, 1, @Date), 113)\r\n ), @MonthStartDW = ((DatePart(dw, @Date) + @@DateFirst - 3) % 7) + 1,\r\n @Month = DatePart(MONTH, @Date);\r\n \r\n SELECT \r\n Max(case when day=1 and monthdate between 1 and @MonthLength then convert(varchar(2),monthdate)+': ' else '' end)\r\n + String_agg(case when day=1 and monthdate between 1 and @MonthLength THEN ISD.name+'. ' ELSE ''END ,'') AS Mon,\r\n Max(case when day=2 and monthdate between 1 and @MonthLength then convert(Varchar(2),monthdate)+': ' else '' end)\r\n + String_agg(case when day=2 and monthdate between 1 and @MonthLength THEN ISD.name+'. ' ELSE ''END ,'') AS Tue, \r\n Max(case when day=3 and monthdate between 1 and @MonthLength then convert(Varchar(2),monthdate)+': ' else '' end)\r\n + String_agg(case when day=3 and monthdate between 1 and @MonthLength THEN ISD.name+'. ' ELSE ''END ,'') AS Wed, \r\n Max(case when day=4 and monthdate between 1 and @MonthLength then convert(Varchar(2),monthdate)+': ' else '' end)\r\n + String_agg(case when day=4 and monthdate between 1 and @MonthLength THEN ISD.name+'. ' ELSE ''END ,'') AS Thu,\r\n Max(case when day=5 and monthdate between 1 and @MonthLength then convert(Varchar(2),monthdate)+': ' else '' end)\r\n + String_agg(case when day=5 and monthdate between 1 and @MonthLength THEN ISD.name+'. ' ELSE ''END ,'') AS Fri, \r\n Max(case when day=6 and monthdate between 1 and @MonthLength then convert(Varchar(2),monthdate)+': ' else '' end)\r\n + String_agg(case when day=6 and monthdate between 1 and @MonthLength THEN ISD.name+'. ' ELSE ''END ,'') AS Sat, \r\n Max(case when day=7 and monthdate between 1 and @MonthLength then convert(Varchar(2),monthdate)+': ' else '' end)\r\n + String_agg(case when day=7 and monthdate between 1 and @MonthLength THEN ISD.name+'. ' ELSE ''END ,'') AS Sun \r\n FROM\r\n (\r\n SELECT DayNo.number AS day, Weekno.number AS week,\r\n (DayNo.number + ((Weekno.number - 1) * 7)) - @MonthStartDW AS monthDate\r\n FROM (VALUES (1), (2), (3), (4), (5), (6), (7)) AS DayNo (number)\r\n CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6)) AS Weekno (number)\r\n ) AS f\r\n LEFT OUTER JOIN #IrishSaintsDay AS ISD\r\n ON f.monthDate = DatePart(DAY, saintsDay)\r\n AND DatePart(MONTH, saintsDay) = @Month\r\n GROUP BY week --so that each week is on a different row\r\n HAVING Max(CASE WHEN f.day = 1 AND f.monthDate BETWEEN 1 AND @MonthLength THEN\r\n f.monthDate ELSE 0 END\r\n ) > 0\r\n OR (f.week = 1 AND Sum(monthDate) > -21);\r\n<\/pre>\n <\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/06\/word-image-3.png\")
<\/p>\n
I\u2019m 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\u2019t 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.<\/p>\n
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<\/a> you are being asked for?<\/p>\n