Have Geek Brain

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

I would like to get all tags that match the specified id, where:

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

Only now I would like to use this as a subquery -

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

becomes:

10 | soup,nuts,juice,milk | Mom
+2


a source to share


3 answers


This is actually quite a difficult thing to believe or not. The best link I've seen for this is here . The following query was taken from the example in this article. There are more options.



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


a source


Check this one . Free subscription required (if you are an sql programmer you should have access to this site anyway)



0


a source


Take a look at the PIVOT command . This won't do what you want, but it looks like. If you wanted to literally do this, that is, turn the dataset into a comma-separated string scalar value, you would need to create a user-defined scalar function that took a table parameter.

0


a source






More articles:


All Articles