CODEWITHSUNDEEP
sqlt-sqlsql-server-2008r2-express
Mubashir Siddique
Mubashir Siddique

Reputation: 11

How to concatenate items having same Id using STUFF

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

Upvotes: -4

Views: 4093

Answers (1)

John Woo
John Woo

Reputation: 263723

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

Upvotes: 2

Related Questions