We help IT Professionals succeed at work.

Flatten one to many relationship

Medium Priority
545 Views
Last Modified: 2011-10-03
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?
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Hello spathiphylum,

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


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

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.