如何将多列数据进行分组合并？

Question

如何将多列数据进行分组合并？

6

假设有这样一张表：

PruchaseID | Customer | Product  | Method
-----------|----------|----------|--------
 1         | John     | Computer | Credit
 2         | John     | Mouse    | Cash
 3         | Will     | Computer | Credit
 4         | Will     | Mouse    | Cash
 5         | Will     | Speaker  | Cash
 6         | Todd     | Computer | Credit

我希望生成一个关于每个顾客购买和付款方式的报告。
但我希望报告以每个顾客为一行的形式呈现，例如：

Customer | Products                 | Methods
---------|--------------------------|--------------
 John    | Computer, Mouse          | Credit, Cash
 Will    | Computer, Mouse, Speaker | Credit, Cash
 Todd    | Computer                 | Credit

到目前为止，我发现可以使用XML PATH方法进行分组连接，例如：

SELECT
    p.Customer,
    STUFF(
        SELECT ', ' + xp.Product
        FROM Purchases xp
        WHERE xp.Customer = p.Customer
        FOR XML PATH('')), 1, 1, '') AS Products,
    STUFF(
        SELECT ', ' + xp.Method
        FROM Purchases xp
        WHERE xp.Customer = p.Customer
        FOR XML PATH('')), 1, 1, '') AS Methods
FROM Purchases

这个查询可以得到结果，但我担心它的速度。
乍一看，这里有三个不同的查询，其中两个将分别与Purchases的行数相乘。这最终会使速度呈指数级下降。

那么，有没有更高效的方法来做到这一点？
我想要添加更多的列来汇总，我应该为每个列都做一个STUFF()块吗？这听起来对我来说不够快。有什么建议吗？

- BeemerGuy

你正在对数据进行反规范化以实现此目的，因此性能可能会成为一个潜在的挑战。使用XML方法将数据反规范化为分隔列表是最佳方式。 - Sean Lange

在使用for xml path时要小心，如果你的数据中有例如&这样的字符，它可能会让你感到惊讶。Aaron Bertrand对不同方法进行了比较，你可能会想要查看一下。 - James Z

3个回答

2

另一个解决方案是使用CLR方法进行组合并，@aaron bertrand在这里进行了性能比较。如果您可以部署CLR，则可以从https://orlando-colamatteo.github.io/ms-sql-server-group-concat-sqlclr/下载免费的脚本，并且所有细节都在文档中。您的查询将变成像这样。

SELECT Customer,dbo.GROUP_CONCAT(product),dbo.GROUP_CONCAT(method)
FROM Purchases
GROUP BY Customer

这个查询很短，易于记忆和使用，XML方法也可以完成任务，但是记住代码有点困难（至少对我来说），并且会出现像XML实体化这样的问题，可以解决，他的博客中还描述了一些陷阱。

另外从性能角度考虑，使用.query是耗时的，我也遇到了性能问题。我希望你能在https://dba.stackexchange.com/questions/125771/multiple-column-concatenation中找到我提出的这个问题，查看肯尼思·费舍尔给出的版本2，一个嵌套的XML连接方法或者由spaggettidba建议的一个unpivot/pivot方法。

- Biju jose

1

这是递归CTE（通用表达式）的一个用例。您可以在此处https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx了解更多信息。

;
WITH CTE1 (PurchaseID, Customer, Product, Method, RowID)
AS
(
    SELECT 
        PurchaseID, Customer, Product, Method, 
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Customer)
    FROM
        @tbl 
        /* This table holds source data. I ommited declaring and inserting 
        data into it because that's not important. */
)
, CTE2 (PurchaseID, Customer, Product, Method, RowID)
AS
(
    SELECT 
        PurchaseID, Customer, 
        CONVERT(VARCHAR(MAX), Product), 
        CONVERT(VARCHAR(MAX), Method), 
        1
    FROM 
        CTE1 
    WHERE 
        RowID = 1
    UNION ALL
    SELECT 
        CTE2.PurchaseID, CTE2.Customer, 
        CONVERT(VARCHAR(MAX), CTE2.Product + ',' + CTE1.Product), 
        CONVERT(VARCHAR(MAX), CTE2.Method + ',' + CTE1.Method), 
        CTE2.RowID + 1 
    FROM 
        CTE2 INNER JOIN CTE1 
            ON CTE2.Customer = CTE1.Customer
            AND CTE2.RowID + 1 = CTE1.RowID
)

SELECT Customer, MAX(Product) AS Products, MAX(Method) AS Methods 
FROM CTE2 
GROUP BY Customer

输出：

Customer    Products                Methods
John        Computer,Mouse          Credit,Cash
Todd        Computer                Credit
Will        Computer,Mouse,Speaker  Credit,Cash,Cash

- Nikhil Vartak

2

嗨，@JamesZ在上面发布了一个链接，其中包含一份性能比较报告。你可以看一下。你的代码虽然可用，但是它的性能非常低... - Shnugo

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Devart · Accepted Answer

一个想法:

DECLARE @t TABLE (
    Customer VARCHAR(50),
    Product VARCHAR(50),
    Method VARCHAR(50),
    INDEX ix CLUSTERED (Customer)
)

INSERT INTO @t (Customer, Product, Method)
VALUES
    ('John', 'Computer', 'Credit'),
    ('John', 'Mouse', 'Cash'),
    ('Will', 'Computer', 'Credit'),
    ('Will', 'Mouse', 'Cash'),
    ('Will', 'Speaker', 'Cash'),
    ('Todd', 'Computer', 'Credit')

SELECT t.Customer
     , STUFF(CAST(x.query('a/text()') AS NVARCHAR(MAX)), 1, 2, '')
     , STUFF(CAST(x.query('b/text()') AS NVARCHAR(MAX)), 1, 2, '')
FROM (
    SELECT DISTINCT Customer
    FROM @t
) t
OUTER APPLY (
    SELECT DISTINCT [a] = CASE WHEN id = 'a' THEN ', ' + val END
                  , [b] = CASE WHEN id = 'b' THEN ', ' + val END
    FROM @t t2
    CROSS APPLY (
        VALUES ('a', t2.Product)
             , ('b', t2.Method)
    ) t3 (id, val)
    WHERE t2.Customer = t.Customer
    FOR XML PATH(''), TYPE
) t2 (x)

输出：

Customer   Product                    Method     
---------- -------------------------- ------------------
John       Computer, Mouse            Cash, Credit
Todd       Computer                   Credit
Will       Computer, Mouse, Speaker   Cash, Credit

另一个具有更多性能优势的想法：

IF OBJECT_ID('tempdb.dbo.#EntityValues') IS NOT NULL
    DROP TABLE #EntityValues

DECLARE @Values1 VARCHAR(MAX)
      , @Values2 VARCHAR(MAX)

SELECT Customer
     , Product
     , Method
     , RowNum = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY 1/0)
     , Values1 = CAST(NULL AS VARCHAR(MAX))
     , Values2 = CAST(NULL AS VARCHAR(MAX))
INTO #EntityValues
FROM @t

UPDATE #EntityValues
SET 
      @Values1 = Values1 =
        CASE WHEN RowNum = 1 
            THEN Product
            ELSE @Values1 + ', ' + Product 
        END
    , @Values2 = Values2 = 
        CASE WHEN RowNum = 1 
            THEN Method
            ELSE @Values2 + ', ' + Method
        END

SELECT Customer
      , Values1 = MAX(Values1) 
      , Values2 = MAX(Values2)
FROM #EntityValues
GROUP BY Customer

但有一些限制：

Customer      Values1                       Values2
------------- ----------------------------- ----------------------
John          Computer, Mouse               Credit, Cash
Todd          Computer                      Credit
Will          Computer, Mouse, Speaker      Credit, Cash, Cash

同时也可以查看我关于字符串聚合的旧文章：

http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server