Flatten one to many relationship

Let's say I have a table ShoppingList  (with ID, ItemName)

1, "Fruit"
1, "Eggs"
1, "Meat"
2, "Fruit"

I would like to have output that looks like this:
1,  "Eggs, Fruit, Meat",  3 (number of items in the list)
2,  "Fruit", 1

also, note that i would like the items alphabetized if possible.

possible?  stored procedure?  std SQL?
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Hello spathiphylum,

create function dbo.ConcatList ( @id int )
returns varchar(8000)
      declare @res varchar(8000)
      select @res = coalesce(@res + ',', '') + itemName
      from ShoppingList  
      where id = @id
      return (@res)

and use it like this:

select s.id, dbo.ConcatList(id) as list, (SELECT COUNT(*) from ShoppingList   where id = s.id )  cnt
from ShoppingList  s
group by id

Aneesh R
spathiphylumAuthor Commented:
seems kind of strange to create a function that is specific to a table.   is that really the best/only way?
All Courses

From novice to tech pro — start learning today.