How to concatenate items having same Id using STUFF

Question

I have three tables as follows:

OrderProductVariant

Id  |   ProductVariantId
----------------------------------------
1   |   22 
2   |   23
3   |   24
4   |   25

ProductVariant

Id  |   ProductId
----------------------------------------
22  |   34
22  |   35
23  |   36
23  |   37
24  |   38
24  |   39

Product

Id  |   Product
----------------------------------------
34  |   KBDMouse800 
35  |   KBDMK250
36  |   LaptopCorei7
37  |   LaptopCorei5
38  |   BluetoothMouse1000
39  |   PresentorR800

I want the output result to be :

OrderProductVariant.Id  |   Product
-----------------------------------------
1           |   KBDMouse800, KBDMK250
2           |   LaptopCorei7, LaptopCorei5
3           |   BluetoothMouse1000, PresentorR800

score 2 · Answer 1 · edited Sep 20 '19 at 10:01

The current query will give all the records from table OrderProductVariant. Maybe it's time for you to figure out how to filter non matching records.

SELECT  o.ID,
        STUFF((SELECT   ',' + ' ' + b.Product 
               FROM ProductVariant a
               INNER JOIN Product b ON a.ProductId = b.Id
               WHERE a.Id = o.ProductVariantId
               FOR XML PATH ('')), 1, 1, '')  AS ProductList
FROM    OrderProductVariant AS o
GROUP   BY o.ID, o.ProductVariantId

SQLFiddle Demo

How to concatenate items having same Id using STUFF

1 Answers1