Convert sql subquery to list or varchar

Question

Hi,

I want to know if we can convert a subquery result to comma separated list in varchar datatype.

For eg. If I have a product table. And I have product image table with foreign key of product. now I want to list all product with select query that should have a column with list of productImage table's pk list for each product.

I'm using sql server 2005. Can we achieve the above in any way?

Answer 1

Not easily, no. I've found the best way to do these things is to execute the subquery on its own, and assemble the comma-separated list programmatically.

If you really need to do it on the database, you could define a scalar-valued function that you can give the value for the foreign key, and which returns the comma-separated list. You'll have to use a cursor inside that function to make the magic happen though.

Answer 2

Hi

I'm not sure if I'm following you, but maybe you can try to add an extra table:

Table Product Table Images Table Product_Images.

In this last table you'll have at least 3 columns, PK for Product_Images table, Product_FK and Images_FK. Then with just

SELECT Image_FK FROM Product_Images WHERE Product_Images.Product_FK = ##;

you'll have a list of Images' PK associated with a product

Hope this will help you, regards.

Answer 3

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)

Here are test data I used for this query

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 

And here is result of query:

ProductID ImageList
--------- ---------
        1 1,2,3
        2 4,5
        3 1
        4 3,5

If you want to have ProductID 5 in the list with null for Image list, just remove next line from query:

Where p.ProductID in (Select ProductID From @ProductImages)

You will have one more row in the result (it does not have images assigned):

        5 null