Group by - How to concatenate Grouped column

Question

I'm stuck with this, please provide some guidance.

I have 3 column which I am grouping by SalesDateTime using DATEPART

So far everything works great but I would like to add a 3rd column that will contain the concatenated product description e.i Good product 1,Good product 2,Good product 3

Here is my script

CREATE TABLE #Sales
(
    Name VARCHAR (100),
    SalesDateTime DATETIME,
    Description VARCHAR (100)     
)


GO
INSERT INTO #Sales
SELECT 'Product1',
       '2012-04-01 00:00:00.000',
       'Good product 1'
UNION ALL
SELECT 'Product2',
       '2012-04-02 00:00:00.000',
       'Good product 2'
UNION ALL
SELECT 'Product3',
       '2012-04-02 00:00:00.000',
       'Good product 3'
UNION ALL
SELECT 'Product4',
       '2012-04-03 00:00:00.000',
       'Good product 4'
UNION ALL
SELECT 'Product5',
       '2012-04-03 00:00:00.000',
       'Good product 5'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000',
       'Good product 6'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000',
       'Good product 7'
UNION ALL
SELECT 'Product1',
       '2012-05-02 00:00:00.000',
       'Good product 8'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000',
       'Good product 9'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000',
       'Good product 10'


GO

--GROUP BY DAY of Year
SELECT   count(*) AS SalesCount, DATEPART(dayofyear,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(dayofyear,SalesDateTime)

GO 
DROP TABLE #Sales

I won't mark duplicate, but have a read here: https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 ... the highly upvoted answers there should cover your problem. — Tim Biegeleisen, Jan 11 '18 at 15:36
Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Tab Alleman, Jan 11 '18 at 15:53

score 0 · Answer 1 · answered Jan 11 '18 at 16:12

0

So you can just use GROUP_CONCAT on that column

It is tailor made for this case

SELECT count(*) AS SalesCount, 
       DATEPART(dayofyear,SalesDateTime) AS Day, 
       GROUP_CONCAT(Description SEPARATOR ',') AS details
FROM #Sales
GROUP BY DATEPART(dayofyear,SalesDateTime)

...I 've recently learned about function :-)

answered Jan 11 '18 at 16:12

Nomikos Strigkos

113
2

GROUP_CONCAT is a MySQL function. Users of SQL Server 2017, and above, can use [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql), which is similar. – David Rushton Jan 11 '18 at 16:41

score 0 · Answer 2 · answered Jan 11 '18 at 16:52

Thank you for each of your answers that guided me in the right path!

The solution

SELECT   count(*) AS SalesCount, DATEPART(dayofyear,SalesDateTime) Day, ISNULL(MAX(Description),'') +','+ ISNULL(MAX(Description),'') +','+ ISNULL(MAX(Description),'') AS FinalColumn
FROM #Sales
GROUP BY DATEPART(dayofyear,SalesDateTime)

GO 
DROP TABLE #Sales

I Concatenated using ISNULL(MAX(Description),'')

Group by - How to concatenate Grouped column

2 Answers2