Mimic group_concat() combined with GROUP BY

Question

I have a table 'booking' like this:

booking_id,
date,
client,
sponsor

I'm trying to get a monthly summary:

SELECT 
  MONTH(date) AS M,
  Sponsor,
  Client,
  COUNT(booking_id) AS c
FROM booking
GROUP BY
 M, Sponsor, Client

Now I want to see at which dates the client made bookings. I tried using STUFF() (referenced in this post: Simulating group_concat MySQL function in Microsoft SQL Server 2005?) but it conflicts with the group-by statement.

Sample data as per request. Currently i have the following:

M       Sponsor     Client  c     
March   AB          y       3
March   FE          x       4
April   AB          x       2

Desired output:

M       Sponsor     Client  c   dates
March   AB          y       3   12, 15, 18
March   FE          x       4   16, 19, 20, 21
April   AB          x       2   4, 8

Where the numbers are the day-numers (e.g. 12 march, 15 march, 18 march). In mysql I would use group_concat(date) to get the last column.

Big kudos for the answer :-)

In addition to showing the query you're currently running, could you please show some sample data and desired results? Word problems and reverse engineering an existing query are both fun exercises, but visualizing the whole problem might lead to a better solution. — Aaron Bertrand, Apr 24 '12 at 13:26
@Lamak: listed under 'desired'. I'm struggling to get the last column, dates. In mysql you would use group_concat but in t-sql there is no such thing. — Lennart, Apr 24 '12 at 13:37
Where do 12, 15, 18 come from? Is there sample data you're not including? When we ask for sample data, we don't mean the result of your monthly summary query. What's actually in the booking table (just the rows that contribute to the desired output)? — Aaron Bertrand, Apr 24 '12 at 13:50

Aaron Bertrand · Accepted Answer · 2022-01-24T13:04:37.013

SELECT [Month] = DATENAME(MONTH, M), Sponsor, Client, c,
  [dates] = STUFF((SELECT N', ' + RTRIM(DATEPART(DAY, [date])) 
    FROM dbo.booking AS b
    WHERE b.Sponsor = x.Sponsor
      AND b.Client = x.Client
      AND b.[date] >= x.M AND b.[date] < DATEADD(MONTH, 1, x.M) 
    ORDER BY [date]
    FOR XML PATH(''),
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, N'')
FROM 
(
  SELECT 
      M = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
      Sponsor,
      Client,
      COUNT(booking_id) AS c
    FROM dbo.booking
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
      Sponsor,
      Client
) AS x
ORDER BY M, Sponsor, Client;

Note that if a combination of sponsor/client has two bookings on the same day, the day number will appear in the list twice.

EDIT Here is how I tested:

DECLARE @booking TABLE
( 
  booking_id INT IDENTITY(1,1) PRIMARY KEY,
  [date] DATE,
  Sponsor VARCHAR(32),
  Client VARCHAR(32)
);

INSERT @booking([date], Sponsor, Client) VALUES
('20120312','AB','y'), ('20120315','AB','y'), ('20120318','AB','y'),
('20120316','FE','x'), ('20120319','FE','x'), ('20120321','FE','x'), 
('20120320','FE','x'), ('20120404','AB','x'), ('20120408','AB','x');

SELECT [Month] = DATENAME(MONTH, M), Sponsor, Client, c,
  [dates] = STUFF((SELECT ', ' + RTRIM(DATEPART(DAY, [date])) 
    FROM @booking AS b
    WHERE b.Sponsor = x.Sponsor
      AND b.Client = x.Client
      AND b.[date] >= x.M AND b.[date] < DATEADD(MONTH, 1, x.M) 
    ORDER BY [date]
    FOR XML PATH(''),
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, N'')
FROM 
(
  SELECT 
      M = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'),
      Sponsor,
      Client,
      COUNT(booking_id) AS c
    FROM @booking
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [date]), '19000101'), 
      Sponsor, 
      Client
) AS x
ORDER BY M, Sponsor, Client;

Results:

Month   Sponsor Client  c       dates
------- ------- ------- ------- --------------
March   AB      y       3       12, 15, 18
March   FE      x       4       16, 19, 20, 21
April   AB      x       2       4, 8

Thx Aaron. I have to wiggle around a bit with it (database is normalized of course) but I'm very happy so far :-) — Lennart, Apr 25 '12 at 08:55
Note that this method, though widely popular, uses undocumented behavior of `FOR XML PATH('')`. That means Microsoft neither tests nor guarantees that it will continue to function in this manner, and it is not guaranteed to be deterministic. The Microsoft Answer to this question is still "That's an application problem, not a database problem." even though the rest of the RDBMS world has implemented this type function. — Bacon Bits, Jan 22 '15 at 22:02
@BaconBits sure. You might find [the comments on these posts](http://stackoverflow.com/questions/9576683/replace-values-in-a-csv-string) interesting. — Aaron Bertrand, Jan 22 '15 at 22:21
@AaronBertrand I'm just a fan of pointing out that it's fundamentally a broken method because it's not supported by the author (even if support reps suggest it as a workaround). If devs don't know that it's undocumented and therefore unsupported, they won't put pressure on Microsoft to just implement `GROUP_CONCAT() [OVER()]` like they should have done years ago. Microsoft Connect is *full* of requests for string aggregates, and MS continues to ignore them. Devs should be angry about it, not accepting workarounds with shockingly arcane syntax that leverage totally unrelated features. — Bacon Bits, Jan 23 '15 at 03:14

Mimic group_concat() combined with GROUP BY

1 Answers1

Linked

Related