May 6, 2009 at 11:27 am
I have a data retrieval problem that I need some help with. I have a SQL database that has a Request table, with RequestID as the primary key. The database also has an AdverseParty table, which uses RequestID as a foreign key. The relationship between Request and AdverseParty is one-to-many, i.e., there can be many rows in Adverse Party that correspond to one row in Request. This database is used by an app that I inherited and that I prefer to modify as little as possible, since it's quite complex and reminds me of a house of cards in a windstorm. Having said that, this app makes extensive use of a series of database views. These views pull all rows from Request, along with information about each row from related tables. I recently discovered a problem with these views: they aren't pulling any information from AdverseParty, although they're supposed to. Instead, they're pulling from an old table that has a one-to-one relationship to Request. Unfortunately, that table is no longer used, and AdverseParty should be used instead. So I'm trying to go from something like this:
SELECT R.RequestID. R.Name, O.AdverseParty
FROM Request R
LEFT OUTER JOIN OldTable O
ON R.RequestID = O.RequestID
to a SELECT statement that uses the AdverseParty table instead of the OldTable table. To do that, I need to gather up all the AdverseParty information by RequestID and display it as a string. As an example, let's say I have a row whose RequestID is 42 in the Request table, and that row's Name field is Acme Corp. I also have 3 rows in the AdverseParty table where RequestID = 42. Those 3 rows look like this:
APID RequestID AdverseParty
1 42 John Doe
2 42 Jane Doe
3 42 Joe Blow
I want to output the following:
RequestID Name AdverseParty
42 Acme Corp John Doe; Jane Doe, Joe Blow
Note that the views do not take RequestID as a parameter; they have no parameters. Rather, they output all requests with their corresponding adverse parties. So the desired output, above, will be only one row of many in the eventual output. I've tried using cursors to populate a temp table with one row per RequestID and all AdverseParty info rolled into one string, but I get an error message telling me that temp tables are not permitted in views. So - I'm stumped. Frankly, I'd prefer a set-based approach to using a temp table and cursors anyway, but I don't know how to accomplish that. Can anyone help?
May 6, 2009 at 8:54 pm
Search this forum for "Concatenation Function".
Create scalar function to accept RequestID and return all corresponding "AdverseParty" values concatenated in one string.
Then you may call it like this:
SELECT R.RequestID. R.Name, dbo.AdversPartForRequest(R.RequestID) AdverseParty
FROM Request R
_____________
Code for TallyGenerator
May 6, 2009 at 9:28 pm
That's how the function should look like:
CREATE FUNCTION AdversePartiesForRequest
(@RequestID int)
RETURNS nvarchar(4000)
AS
BEGIN
declare @AdverseParties nvarchar(4000)
SELECT @AdverseParties = ISNULL(@AdverseParties+ ', ', '') + AdverseParty
FROM dbo.AdverseParty
WHERE @RequestID = RequestID
ORDER BY ??? -- if you need to list the parties in any particular order
RETURN @AdverseParties
END
_____________
Code for TallyGenerator
May 7, 2009 at 8:19 am
Sergiy, thanks so much, this works beautifully! 😀 😀
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply