Get multiple column values as one item?

[Spoogledrummer]

Hi, I'm attempting to do a query that will return all values for a specific person under one alias. e.g I have a users table with names in such as Fred, Bob, Jim. Then I have a devices table of items they all use, say Fred uses a Phone, PC, Printer and Fax and they were linked on userid would I be able to run a query that instead of returning:

Name-----Device

Fred ------ Phone
Fred ------ PC
Fred ------ Printer
Fred ------ Fax


I would get:

Name ----- Device
Fred ------ Phone, PC, Printer, Fax

Sorry if it's not clear but without being able to draw up tables it's hard to explain.

[Spoogledrummer]

In effect I want to return more than 1 value from a subquery

[Delerna]

Here is a similar Question with an answer.

[Jim Doherty]

Hi, I'm attempting to do a query that will return all values for a specific person under one alias. e.g I have a users table with names in such as Fred, Bob, Jim. Then I have a devices table of items they all use, say Fred uses a Phone, PC, Printer and Fax and they were linked on userid would I be able to run a query that instead of returning:

Name-----Device

Fred ------ Phone
Fred ------ PC
Fred ------ Printer
Fred ------ Fax


I would get:

Name ----- Device
Fred ------ Phone, PC, Printer, Fax

Sorry if it's not clear but without being able to draw up tables it's hard to explain.

Hi Spoogledrummer,

This should work for you if it fits your table names and field names if not then obviously amend

Assumptions
You have a table Called tblUsers with Fields
UserID (datatype - int identity)
UserName (datatype - text)

You have a table called tblDevice with Fields
DeviceID (datatype int identity)
UserID (datatype int)
Device (datatype text)

Requirement
To retrieve all devices from the Devices table associated with a specific UserID from the Users table and in so doing present the device names contained in the Devices table concatenated as a continous single field line separating the respective values with a comma and then a space.

Functional Resolution
Create a user defined function having the following syntax

 CREATE FUNCTION dbo.UDF_RetrieveDevice 
( @UserID int ) 
RETURNS varchar(500) 
AS 
BEGIN 
DECLARE @DeviceString varchar(255), @delimiter char 
SET @delimiter = ',' 
SELECT @DeviceString = COALESCE(@DeviceString + @delimiter, '') +SPACE(1)+ Device
FROM (SELECT DISTINCT Device FROM dbo.tblDevice where UserID=@UserID) derived_devices
RETURN ( SELECT LTRIM(@DeviceString) AS [Device]) 
END
 

Implementation method
Call the function from a view/query by typically using this SQL syntax

SELECT UserID, UserName, UDF_RetrieveDevices(UserID) as Device FROM dbo.tblUsers

Comments & considerations
Note the defined varchar lengths within the body of the function to amend to reflect your needs
The resultant SQL statement when run as a view/query outlined above should present you with what you require from your posting

Regards

Jim :)

[Delerna]

well thanks very much Jim
I learnt something today

[Spoogledrummer]

Excellent, thanks very much.

[Jim Doherty]

Hi guys,

You're very welcome

Jim :)