SQL Server - Concatenate ID field while using group by

Question

I am currently working on developing some association mining for a table that contains product information. Put simply, the core table essentially looks like this:

I am currently grouping by the product and counting the total amount of distinct transaction IDs to see how many transactions a specific product is part of. This then turns the table into this:

My question is, once I do the group by is there a way to pull out the actual transaction ID without getting rid of the grouping? Maybe a way to concatenate the IDs into another column so it would look something like this:

Definitely open to other suggestions on how to get back to the original IDs. Thank you!

Here is the answer for you question https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value#answers-header — Sowmyadhar Gourishetty, Aug 20 '20 at 17:31

score 0 · Answer 1 · answered Aug 20 '20 at 17:34

0

SQL Server 2016+

SELECT 
    product
   ,count([transaction id])  as [count of transaction id]
   ,STRING_AGG(ID,',') as IDs
FROM YourTable
GROUP BY product

answered Aug 20 '20 at 17:34

Gabriel Durac

2,610
1
12
13

score 0 · Answer 2 · answered Aug 20 '20 at 17:41

0

You can try this. DBFIDDLE

SELECT a.Product
    ,a.[count of transactions]
    ,SUBSTRING(a.all_transactions, 0, LEN(a.all_transactions)) [IDs]
FROM (
    SELECT Product
        ,COUNT(DISTINCT TransactionId) [count of transactions]
        ,(
            SELECT DISTINCT CAST(TransactionId AS VARCHAR(10)) + ',' [text()]
            FROM dbo.Products inner_p
            WHERE inner_p.Product = Products.Product
            --GROUP BY inner_p.Product
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)') [all_transactions]
    FROM dbo.Products
    GROUP BY Product
    ) a

Output:

answered Aug 20 '20 at 17:41

Sowmyadhar Gourishetty

1,843
1
8
15

Hi Sowmyadhar, this got the job done for single product combinations! I was wondering if it is also possible to follow this sort of logic for double product combinations? So the total count of transactions that have both apples and oranges and the IDs that contain both. Hope that makes sense. – John Aug 20 '20 at 18:20
I didn't understand the double product combinations, can you update your question with sample example. – Sowmyadhar Gourishetty Aug 20 '20 at 18:27

SQL Server - Concatenate ID field while using group by

2 Answers2