Aggregate function that append columns values of each group?

Question

I have a similar table to this:

Id     Name    CommitDate  Commits
------ ------- ----------- --------
1      Ahmed   2018-07-05  2
1      Ahmed   2018-07-07  6

I need this output:

Id     Name    Summary
------ ------- -------------------------------------------------
1      Ahmed   2 commits on 2018-07-05\n 6 commits on 2018-07-07

Actually, I stopped at this:

SELECT Id, Name, '' AS Summary  FROM Commits
GROUP BY Id, Name

and couldn't continue, as every GROUP BY clause I used before came with a simple aggregate function, I'm unable to figure out if there is an aggregate function that can append grouped columns together!

if you use SQL Server 2017, then you may use STRING_AGG: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 — Radim Bača, Jul 11 '18 at 08:54

Andomar · Answer 1 · 2018-07-11T09:01:14.593

In SQL Server 2017 or later, you can use string_agg:

select  id
,       name
,       string_agg(cast(commits as nvarchar(5)) + ' commits on ' + 
                   convert(nvarchar(10), commitdate, 120), char(10)) + char(10)
from    commits
group by
        id
,       name

You can express newline in SQL Server like char(10), or for a linefeed plus newline, char(13) + char(10). The 120 style converts a datetime to yyyy-mm-dd format.

Working example on SQL Fiddle.

Aggregate function that append columns values of each group?

1 Answers1