1

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
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"

Community
  • 1
  • 1
Scottster
  • 23
  • 2
  • 5

2 Answers2

0

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)

Unreason
  • 12,556
  • 2
  • 34
  • 50
  • Looks like that would work, but for better or worse we are using Microsoft SQL. Thank you for the response. – Scottster Dec 10 '10 at 20:07
0

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;
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • I don't think that will work for my needs. When I look up the syntax for STUFF it shows "STUFF (character_expression , start , length ,character_expression)" Where Character_expression is a string, and the second Character_expression is getting inserted into the first string at the "start" location. I need to be able to get results from different columns. ID Name Relation Address 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 I need the results tolook something like... "1 DAU, SON" – Scottster Dec 10 '10 at 20:34