sql - 如何分组连接多列？

Question

假设这张表:

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() block 吗？这对我来说听起来不够快。

建议？

Answer 1

只是一个想法:

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