Combine 2 rows with same id into 1 column-CodePudding

I have tried to combine below data according to the result shown as below in sql server.

Raw data:

Expected result:

i am not sure if i should use pivot. also, when i tried to concat it only able to combine the data from the same row. It such a great help if you can help me with this. :)

CodePudding user response：

Looks like the answer from How to turn one column of a table into a csv string in SQL Server without using a cursor with string_agg should do the trick

SELECT
    salesid, 
    loyaltyid, 
    STRING_AGG(paymentitem, ' ') AS paymentItem
FROM rawdata 
GROUP BY salesId, loyaltyId
ORDER BY salesId, loyaltyId;

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

CodePudding user response：

In MYSQL:

SELECT
    salesid, 
    loyaltyid, 
    GROUP_CONCAT(text, ' ') AS paymentItem
FROM ABC 
GROUP BY salesId, loyaltyId
ORDER BY salesId, loyaltyId;