SQL Combine duplicate rows while concatenating one column

Question

I have a table (example) of orders show below. The orders are coming in with multiple rows that are duplicated for all columns except for the product name. We want to combine the product name into a comma delimited string with double quotes. I would like to create a select query to return the output format shown below.

INPUT

Name        address             city    zip     product name
-----------------------------------------------------------------
John Smith  123 e Test Drive    Phoenix 85045   Eureka Copper Canyon, LX 4-Person Tent
John Smith  123 e Test Drive    Phoenix 85045   The North Face Sequoia 4 Tent with Footprint
Tom Test    567 n desert lane   Tempe   86081   Cannondale Trail 5 Bike - 2021

OUTPUT

Name        address             city    zip     product name
------------------------------------------------------------------
John Smith  123 e Test Drive    Phoenix 85045   "Eureka Copper Canyon, LX 4-Person Tent", "The 
North Face Sequoia 4 Tent with Footprint"
Tom Test    567 n desert lane   Tempe   86081   Cannondale Trail 5 Bike - 2021

In MySQL [GROUP_CONCAT](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat), in MS-SQL or PostgreSQL [STRING_AGG](https://stackoverflow.com/questions/24906826/how-to-sort-the-result-from-string-agg) — Luuk, Aug 19 '21 at 17:33

score 0 · Answer 1 · answered Aug 19 '21 at 17:46

You can have List_AGG() OR GROUP_CONCAT and then join the results back to original table. Then you can remove duplicates using row_number which will create a same rank if data is same


WITH ALL_DATA AS (

SELECT * FROM TABLE
),

LIST_OF_ITEMS_PER_PRODUCT AS (

SELECT 
   ALL_DATA.NAME,
   LIST_AGG(ALL_DATA.PRODUCT_NAME , ",") AS ALL_PRODUCTS_PER_PERSON
-- IF YOUR SQL DON'T SUPPORT LIST_AGG() THEN USE GROUP_CONCAT INSTEAD
FROM 
  ALL_DATA
GROUP BY 1
),

LIST_ADDED AS (

SELECT
ALL_DATA.*,
LIST_OF_ITEMS_PER_PRODUCT.ALL_PRODUCTS_PER_PERSON

FROM 
  ALL_DATA
LEFT JOIN LIST_OF_ITEMS_PER_PRODUCT 
ON ALL_DATA.NAME = LIST_OF_ITEMS_PER_PRODUCT.NAME
),

ADDING_ROW_NUMBER AS (

SELECT 
* ,

ROW_NUMBER() over (partition by list_added.NAME, ADDRESS, CITY, ZIP ORDER BY NAME) AS ROW_NUMBER_
FROM LIST_ADDED
)

SELECT 

* FROM 

ADDING_ROW_NUMBER

WHERE ROW_NUMBER_ = 1

I have tried using LIST_AGG() and GROUP_CONCAT() and both are not recognized functions. — Nick Kester, Aug 19 '21 at 18:15

SQL Combine duplicate rows while concatenating one column

1 Answers1