asked on

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

Guy Hengel [angelIII / a3]

what is your database?
this should be done in the client application, that is not really a SQL functionality.

cbish21577

ASKER

SQL 2000

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

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

cbish21577

ASKER

Thanks angellll, I'll test this out. Much appreciated.

cbish21577

ASKER

Thanks, that worked perfect.