create an output column-CodePudding

I'm working on a customer level, and I have a column that contains the 3 types of products I'm selling, what I want is to know every customer what products they already purchased? but what if that customer has purchased more than one item? I want to create a column that tell me what they exactly bought 'Coffee','mug' or 'chocolate'.. how can I represent that in the output? again, all these info are stored in one column called 'product'

Thank you

CodePudding user response：

For Snowflake, you can use LISTAGG or ARRAY_AGG depending on whether you want to store the data as a string or as an array. In Snowflake, I would recommend that you store this data as an array, as it is easier to deal with when querying later.

https://docs.snowflake.com/en/sql-reference/functions/array_agg.html

https://docs.snowflake.com/en/sql-reference/functions/listagg.html

CodePudding user response：

You want to use GROUP_CONCAT (mySQL) or STRING_AGG (tSQL)

So you want to do something like this for mysql:

SELECT a.[CustomerID], GROUP_CONCAT(a.[Product])  as Products 
FROM [tblSells] a 
GROUP BY a.[CustomerID];

Or something like this for tsql:

SELECT a.[CustomerID], STRING_AGG(a.[Product],'.') as Products 
FROM [tblSells] a
GROUP BY a.[CustomerID];