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 :)
21
