QPR
asked on
Stringing results together
I have a function (SQL Server) I use to return values - see below
CREATE FUNCTION [dbo].[GetRelatedRecords] (@id bigint)
returns varchar(250)
as
BEGIN
declare @results varchar(250)
SELECT @results = 'Related Record: ' + R.recordid
FROM RECORDS R
left join LINKS L
ON R.id = L.LinkID
WHERE L.id = @id
RETURN @results
END
Assuming the value or R.Recordid = 'XYZ' then this will return 'Related Record: XYZ' which is great, but there are sometimes more than one match for L.id = @id and I'd like them all (rather than the last one which is what is currently returned.
So if the results were XYZ and ABC I need to return ''Related Records: XYZ|Related Records: ABC'
CREATE FUNCTION [dbo].[GetRelatedRecords] (@id bigint)
returns varchar(250)
as
BEGIN
declare @results varchar(250)
SELECT @results = 'Related Record: ' + R.recordid
FROM RECORDS R
left join LINKS L
ON R.id = L.LinkID
WHERE L.id = @id
RETURN @results
END
Assuming the value or R.Recordid = 'XYZ' then this will return 'Related Record: XYZ' which is great, but there are sometimes more than one match for L.id = @id and I'd like them all (rather than the last one which is what is currently returned.
So if the results were XYZ and ABC I need to return ''Related Records: XYZ|Related Records: ABC'
ASKER
This function will be part of a larger select which is why I need the value(s) returned as a single column - how would I loop through the table var to create the string? That would put me in the same situation as my initial question
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi QPR,
The functionality angelIII used is known as the Quirky Update. (You can do this in an update as well.
Sort answer is that the @results = ... expression is evaluated for each row and produces a delimited string with the column results.
HTH
David
The functionality angelIII used is known as the Quirky Update. (You can do this in an update as well.
Sort answer is that the @results = ... expression is evaluated for each row and produces a delimited string with the column results.
HTH
David
ASKER
Excellent (as always)
Create FUNCTION SalesByStore(@storid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM dbo.sales s, dbo.titles t
WHERE s.stor_id = @storid AND t.title_id = s.title_id)
GO
If you really want to return a string (strange) then insert into a table variable loop through it and return the string