Solved

LEFT OUTER JOIN Merge Two Row Fields as One Field

Posted on 2008-06-18
6
1,152 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]
ID: 21816065
what is your database?
this should be done in the client application, that is not really a SQL functionality.
0
 

Author Comment

by:cbish21577
ID: 21816076
SQL 2000
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21816149
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21816155
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
ID: 21816170
Thanks angellll, I'll test this out. Much appreciated.


0
 

Author Closing Comment

by:cbish21577
ID: 31468530
Thanks, that worked perfect.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

14 Experts available now in Live!

Get 1:1 Help Now