How to group concatenation of multiple columns?

Question

Suppose this table:

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

I want to create a report for each client about what they bought and how to pay them.
But I want this report to make one line for each client, for example:

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

What I have found so far is grouping-concatenate using a method XML PATH, for example:

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

It gives me the result, but I am worried about the speed of it.
At first glance, there are three different choices, two of which will be multiplied by the number of lines that Shopping has. This will ultimately slow down.

, ?
, STUFF() ? .

Siggestions?

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

Answer 2

CTE (Common Table Expressions). 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 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

Answer 3

CLR . @aaron bertrand . CLR, script http://groupconcat.codeplex.com/, . . :

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

, , XML , ( ) , , XML, , .

Also, in terms of performance using .query a lot of the time, I had the same performance issues. Hope you can find this question that I raised here at https://dba.stackexchange.com/questions/125771/multiple-column-concatenation . check out version 2 given by kenneth fisher, the nested xml concatenation method, or the innocence / rotation method suggested by spaggettidba.