Solved

LEFT OUTER JOIN Merge Two Row Fields as One Field

Posted on 2008-06-18
6
1,149 Views
Last Modified: 2012-08-13
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
Comment
Question by:cbish21577
  • 3
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
what is your database?
this should be done in the client application, that is not really a SQL functionality.
0
 

Author Comment

by:cbish21577
Comment Utility
SQL 2000
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:cbish21577
Comment Utility
Thanks angellll, I'll test this out. Much appreciated.


0
 

Author Closing Comment

by:cbish21577
Comment Utility
Thanks, that worked perfect.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now