Combining multiple rows of data into a single row based on a shared identifier

Question & Answer
, ,
1

Hi Experts,
Here i need help in data transformation so i will brief my requirement as below.

In this activity, i need to combine multiple rows of data into a single row based on a shared identifier (such as a customer_id) and concatenating the values from these rows into a single column separated by a comma.

Here is my data :
In this data product_name has multiple rows.

Customer_id product_name
1001 laptop
1001 mobile

And I want data like product_names row should have only one row with mutliple values as comma separated values for each customer_id.

customer_id product_name
1001 laptop,mobile

Please try to help me to achieve this requirement.

Thanks
Gururaj

2

Hello @ArnaJi88, this is an update you will want to make in your SQL query when you are creating your dataset. I would suggest grouping by your Customer_id then using the STRING_AGG function grab all of your product_names for each user and seperate them with a comma. It would look something like this (may vary slightly depending on the datasource you are querying):
SELECT Customer_id, STRING_AGG([product_name], ', ') as product_name FROM Table GROUP BY Customer_id

That should convert your product_name field values to your desired output.

3

What if I want to just concat the product name having same customer_id and keep both the records (don’t combine the two records as single), is it possible by using quicksight calculated field?

Current Data:
Customer_id product_name
1001 laptop
1001 mobile

Required Output:
Customer_id product_name
1001 laptop, mobile
1001 laptop, mobile

1 Like
4

Hello @IamPrat15, welcome to the QuickSight community!

Since we have already resolved this question, can you post your response in a new topic in the community? That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!