Return multiple values in one column within a main query

I am trying to find Relative information from a table and return those results (along with other unrelated results) in one row as part of a larger query.

I already tried using this example, modified for my data.

How to return multiple values in one column (T-SQL)?

But I cannot get it to work. It will not pull any data (I am sure it is is user[me] error).

If I query the table directly using a TempTable, I can get the results correctly.

DECLARE @res NVARCHAR(100)  
SET @res = ''  
CREATE TABLE #tempResult ( item nvarchar(100) )  
INSERT INTO #tempResult  
SELECT Relation AS item  
FROM tblNextOfKin  
WHERE ID ='xxx' AND Address ='yyy'  
ORDER BY Relation   
SELECT @res = @res + item + ', ' from #tempResult  
SELECT substring(@res,1,len(@res)-1) as Result  
DROP TABLE #tempResult  

Note the WHERE line above, xxx and yyy would vary based on the input criteria for the function. but since you cannot use TempTables in a function... I am stuck.

The relevant fields in the table I am trying to query are as follows.

tblNextOfKin

ID - varchar(12)

Name - varchar(60)

Relation - varchar(30)

Address - varchar(100)

I hope this makes enough sense... I saw on another post an expression that fits.

My SQL-fu is not so good.

Once I get a working function, I will place it into the main query for the SSIS package I am working on which is pulling data from many other tables.

I can provide more details if needed, but the site said to keep it simple, and I tried to do so.

Thanks !!!

Follow-up (because when I added a comment to the reponse below, I could not edit formatting)

I need to be able to get results from different columns.

ID Name Relation Address

开发者_JAVA百科1, Mike, SON, 100 Main St.

1, Sara, DAU, 100 Main St.

2, Tim , SON, 123 South St.

Both the first two people live at the same address, so if I query for ID='1' and Address='100 Main St.' I need the results to look something like...

"DAU, SON"

Mysql has GROUP_CONCAT

SELECT GROUP_CONCAT(Relation ORDER BY Relation SEPARATOR ', ') AS item  
FROM tblNextOfKin  
WHERE ID ='xxx' AND Address ='yyy' 

You can do it for the whole table with

SELECT ID, Address, GROUP_CONCAT(Relation ORDER BY Relation SEPARATOR ', ') AS item  
FROM tblNextOfKin  
GROUP BY ID, Address 

(assuming ID is not unique)

note: this is usually bad practice as an intermediate step, this is acceptable only as final formatting for presentation (otherwise you will end up ungrouping it which will be pain)

I think you need something like this (SQL Server):

  SELECT stuff((select ',' +Relation 
  FROM tblNextOfKin a
  WHERE ID ='xxx' AND Address ='yyy'  
  ORDER BY Relation
  FOR XML path('')),1,1,'') AS res;