Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1172
  • 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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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