SQL Server Have a select query as part of one select columns

Question

Hey Im just wondering if the following is possible

select id, name, (select name from secondTable where companyId = tableOne.id) as concatenatedString..
from tableOne

so what I am looking for the select to do is for each record return in tableOne, to perform a lookup in another table and return all the values for this record id. (The other table is a lookup table so each id might have 1 or more entries). If more than 1 value is returned in the subquery to build up a single string value, with comma delimited.

so i.e return data would look like

1  Microsoft Bill,Gate,Jack
2  Apple     Jobs

Answer 1

You want to use FOR XML PATH construct:

select 
    ID, 
    Name,
    stuff((select ', ' + Name
           from secondTable where companyId = tableOne.id 
           for xml path('')),
          1,2,'') [Names]
from tableOne

The STUFF function is to get rid of the final ', ' which will be appended at the end.

You can also see another examples here:

• SQL same unit between two tables needs order numbers in 1 cell
• SQL and Coldfusion left join tables getting duplicate results as a list in one column
• SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

Answer 2

I'm not sure why you'd like to skip the join, because it would make your query much more flexible. If you're keen on using a sub-select, you can do this by making your sub-select a table:

SELECT t1.id, t1.name, t2.name
FROM   tableOne t1
INNER JOIN (select id, name from secondTable) AS t2
on t1.id = t2.id

Of course Abe's answer makes more sense if all you're doing is joining on ID.

If you'd like to aggregate over the select in case it returns multiple columns, you can use GROUP BY t1.id, t1.name.