SQL在连接一列时合并重复行

Question

我有一个订单表(示例)如下所示。输入的订单有多行，除了产品名称外，所有列都是重复的。我们希望将产品名称组合成一个带双引号的逗号分隔字符串。我想创建一个选择查询来返回如下所示的输出格式。

输入

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

输出

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

Answer 1

您可以使用List_AGG()或GROUP_CONCAT，然后将结果连接回原始表。然后，您可以使用row_number删除重复项，这将创建相同的排名如果数据相同

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