How to sort & concatenate multiple string values for each user per day in SQL?-CodePudding

I have an instance where my data looks like this:

date       | user_id | product
2020-01-01 | 1       | banana
2020-01-01 | 1       | apple
2020-01-01 | 1       | kiwi

I would like to write a sql query that will output this format by compressing:

date       | user_id | product
2020-01-01 | 1       | apple,banana,kiwi (ordered!)

How would that be possible in SQL? Any suggestions are very much appreciated.

CodePudding user response：

If it is MySQL,

SELECT
t.date
, t.user_id
,GROUP_CONCAT(t.product ORDER BY t.product) as products
FROM
your_table_name t
GROUP BY
t.date, t.user_id

CodePudding user response：

this query based on MSSQL for convert rows value into one comma saparate string based on group by

SELECT date, user_id, product = 
    STUFF((SELECT ', '   product
           FROM your_table b 
           WHERE b.date = a.date and b.user_id = a.user_id 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY date, user_id