Solved

Stringing results together

Posted on 2013-01-17
5
202 Views
Last Modified: 2013-01-21
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'
0
Comment
Question by:QPR
5 Comments
 
LVL 2

Expert Comment

by:MMTadmin
ID: 38789309
Return a table?

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
0
 
LVL 29

Author Comment

by:QPR
ID: 38790205
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
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38794148
this should do:

CREATE FUNCTION [dbo].[GetRelatedRecords] (@id bigint)
returns varchar(250)
as
BEGIN
declare @results varchar(250)
SELECT @results = coalesce(@results + '|' , '' ) + 'Related Record: ' + R.recordid
FROM RECORDS R
left join LINKS L
      ON R.id = L.LinkID
WHERE L.id = @id
RETURN @results
END

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 38795997
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
0
 
LVL 29

Author Closing Comment

by:QPR
ID: 38802687
Excellent (as always)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question