Combine fields from joined tables

Question

One category can have many products. I have to build StoredProcedure that returns all categories with some data from Products combined into one field:

SP Result:
idCategory Name   ProductNames         ProductQuantities 
1          Cat1   Procut1,Product2     24,32
2          Cat2   ProductX,ProductY    0,61

ProductNames and ProductQuantities are varchar fields with combined (concatenated) field values from joined Product tables. This is what I have in DB:

Category table:
idCategory   Name
1            Cat1
2            Cat2

Product table:
idProduct  idCategory Name      Quantity 
1          1          Product1  24
2          1          Product2  32
3          2          ProductX  0
4          2          ProductY  61 

I would also like to have Function, that returns "Product1,Product2" for input parameter idCategory=1, like this:

SELECT idCategory, 
       dbo.NamesFn(idCategory) AS ProductNames,
       dbo.QuantitiesFn(idCategory) AS ProductQuantities 
FROM Category

maybe one function that returns Table Result, so joining would be done only once, not in every Fn (because this is simplified example, in real app I have to have 4-5 combined fields, or even more in the future)?

How to write that SQL / SP & Fn? I'm using MS SQL2005

Answer 1

There are several solutions for concatenating rows using SQL Server. See this, and this.

This query produces the result set you asked for:

select c.idCategory, c.Name,
    replace(
     (select p.Name as [data()]
      from Product p
     where p.idCategory = c.idCategory
     for xml path ('')
), ' ', ', ') as ProductNames,
    replace(
     (select p.Quantity as [data()]
      from Product p
     where p.idCategory = c.idCategory
     for xml path ('')
), ' ', ', ') as ProductQuantities 
from Category c

Edited to add: This query produces the result:

idCategory  Name    ProductNames          ProductQuantities
1           Cat1    Product1, Product2    24, 32
2           Cat2    ProductX, ProductY    0, 61