MS SQL "Stuff" Keyword

[Wednesday, September 22, 2010]

I discovered the "Stuff" keyword for MS SQL today. A pretty vague name for a keyword but it came in handy so I thought I'd make a note of the code that I found on StackOverflow: how to cram subquery results into a single varchar.

Declare @Products Table (ProductID int primary key, ProductName varchar(20))        
Declare @ProductImages Table (ProductId int, ImageId int, Primary Key (ProductId, ImageId))

Insert Into @Products 
Select 1, 'Product1' Union all
Select 2, 'Product1' Union all
Select 3, 'Product1' Union all
Select 4, 'Product1' Union all
Select 5, 'Product1' 

Insert Into @ProductImages
Select 1,1 Union all
Select 1,2 Union all
Select 1,3 Union all
Select 2,4 Union all
Select 2,5 Union all
Select 3,1 Union all
Select 4,3 Union all
Select 4,5 

Select p.ProductID,
       Stuff((Select ','+Cast(ImageID as varchar(10)) 
                From @ProductImages i 
               Where p.ProductID=i.ProductId 
                 For XML PATH('')
             ),1,1,''
            ) as ImageList
  From @Products p
 Where p.ProductID in (Select ProductID From @ProductImages)

Posted by: Benjamin Felt