• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1171
  • Last Modified:

LEFT OUTER JOIN Merge Two Row Fields as One Field

I have the following query that connects dattbl_Case with dattbl_Policy

dattbl_Policy sometimes has more then one row. When this occurs I need my query to merge both of the POL_NUM column rows from this table into one POL_MERGED column in one row.

So instead of:
CASE_ID POL_ID
600000   POL_ID A
600000   POL_ID B
I need
CASE_ID POL_ID
600000   POL_ID A
               POL_ID B

Not sure how to go about doing this.
Thanks in advance.

 
SELECT     dattbl_Case.CASE_ID, dattbl_Policy.POL_NUM as POL_MERGED
FROM         dattbl_Case LEFT OUTER JOIN
                      dattbl_Policy ON dattbl_Case.CASE_ID = dattbl_Policy.CASE_ID
WHERE     (dattbl_Case.CASE_STATUS = 2)
ORDER BY dattbl_Case.CASE_ID

Open in new window

0
cbish21577
Asked:
cbish21577
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is your database?
this should be done in the client application, that is not really a SQL functionality.
0
 
cbish21577Author Commented:
SQL 2000
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need a helper function like this:
CREATE FUNCTION dbo.ConcatPolicyNum(@CaseID int)
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @res VARCHAR(8000)
  SELECT @res = COALESCE(@res + ' ', '') + CAST(POL_NUM AS VARCHAR(20))
    FROM dattbl_Policy
   WHERE Case_ID = @caseID
  RETURN @res
END

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and your query becomes this:
SELECT c.CASE_ID, dbo.ConcatPolicyNum(c.CaseID) as POL_MERGED
  FROM dattbl_Case c
 WHERE c.CASE_STATUS = 2
 ORDER BY c.CASE_ID

Open in new window

0
 
cbish21577Author Commented:
Thanks angellll, I'll test this out. Much appreciated.


0
 
cbish21577Author Commented:
Thanks, that worked perfect.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now