Can JOIN or UNION collapse mutiple rows for use in a subquery

Question

I'd like to get all tags that match a given id where:

select tag where id = 101 //returns 4 rows
soup
nuts
juice
milk

Only now, i'd like to use that as a subquery -

select idList, (select tag where id = 101) itemsOnList, shopper from assignedLists

becomes:

10 | soup,nuts,juice,milk | Mom

Answer 1

Check this out. Free Subscription required (if you're a sql programmer you need to have access to this site anyways)

Answer 2

Have a look at the PIVOT command. It wouldn't do exactly what you want but it is similar. If you wanted to literally do that, i.e. turn a data set into a scalar value of a comma seperated string you would have to create a user defined scalar function that took a table parameter.

Answer 3

This is actually a pretty tricky thing to do believe or not. The best reference I have seen for doing this is here. The following query was taken from an example in that article. There are more options.

select
  idList,
  (
    select name + ','
    from tag
    where id = 101
    for xml path('')
  ) as itemsOnList,
  shopper
from
  assignedLists