[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Combining multiple rows into one row.

Posted on 2006-05-17
7
Medium Priority
?
375 Views
Last Modified: 2008-03-17
I have to combine data from three tables where more than one table has multiple rows into just one row and I have an instance where one relationship has no clear hiearchy. See example of the data below.

TableOne
AccountNo | AccountInfo | AccountStatus | AccountType
     1A               XYZ                Active                6

TableTwo
AccountNo | AccountCustomer | AccountRelationship
    1A                  123                         Primary
    1A                  345                         Secondary
    1A                  678                         Secondary

TableThree
AccountCustomer | AccountCustomerName
     123                           Mr. X
     345                           Ms. Y
     678                           Ms. X


I need to produce the table below from these 3 tables above.

TableFour
AccountNo|PrimaryCustNo|PrimaryAcctName|1stSecondaryCustNo|1stSecondaryAcctName|2ndtSecondaryCustNo  
   1A                123                  Mr. X                       345                        Ms. Y                            678
|2ndSecondaryAcctName
           Ms. X

Thanks!




0
Comment
Question by:Sue_W
  • 4
  • 3
7 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16699419
Is there a limit on the number of customer accounts per account?
0
 

Author Comment

by:Sue_W
ID: 16699633
Up to five account relationships can be associated with a primary account but I only have room for 2 of the possible 5 in my row.

So account: 1A can have a Primary Account - like 123 plus 345 as a Secondary and 456 as a Secondary and 678 as a Secondary and 789 as a Secondary and 987 as a Secondary. The only difference between the accounts is the AccountCustomer ID. These are all in individual rows in TableTwo and TableThree.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16699855
If there were different relationship definitions for the secondaries (secondary1, secondary2, etc) this would be much easier.  What precedence do you want to give for secondary relationships?  Do you just want to pull the first two in order of AccountCustomer or AccountCustomerName?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Sue_W
ID: 16700265
Yes - the relationship definition being the same is my primary issue. I had created a select that returned the row easily as long as I was dealing with hiearchical relationships but once I lost that I couldn't get the relationships to populate customer2 and customer3 correctly.

0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16701937
Below is a function that you can use to return the nth AccountCustomer for a given AccountNo.

CREATE FUNCTION dbo.udf_GetSecondaryAccountCustomer(@AccountNo char(10), @Rank tinyint)
RETURNS int
BEGIN

DECLARE @AccountCustomer int

SELECT IDENTITY(int, 1, 1) AS Rank, AccountNo, AccountCustomer
INTO #TableTwo
FROM TableTwo
WHERE AccountNo = @AccountNo
   AND AccountRelationship = 'Secondary'
ORDER BY AccountCustomer

SELECT @AccountCustomer = AccountCustomer
FROM #TableTwo
WHERE Rank = @Rank

RETURN @AccountCustomer
END

SELECT TableOne.AccountNo,
   PrimRel.AccountCustomer,
   PrimAcct.AccountCustomerName,
   dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 1) AS 1stSecondaryAccountCustomer,
   dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 2) AS 2ndSecondaryAccountCustomer
FROM TableOne
LEFT OUTER JOIN TableTwo AS PrimRel
   INNER JOIN TableThree AS PrimAcct
      ON PrimRel.AccountCustomer = PrimAcct.AccountCustomer
   ON TableOne.AccountNo = PrimRel.AccountNo
   AND PrimRel.AccountRelationship = 'Primary'
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 16701991
SELECT AccountCustomers.AccountNo,
   AccountCustomers.PrimaryAccountCustomer,
   Prim.AccountCustomerName AS PrimaryAcctName,
   AccountCustomers.1stSecondaryAccountCustomer,
   Sec1.AccountCustomerName AS 1stSecondaryAccctName,
   AccountCustomers.2ndSecondaryAccountCustomer,
   Sec2.AccountCustomerName AS 2ndSecondaryAcctName
FROM (
   SELECT TableOne.AccountNo,
      PrimRel.AccountCustomer AS PrimaryAccountCustomer,
      dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 1) AS 1stSecondaryAccountCustomer,
      dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 2) AS 2ndSecondaryAccountCustomer
   FROM TableOne
   LEFT OUTER JOIN TableTwo AS PrimRel
      ON TableOne.AccountNo = PrimRel.AccountNo
      AND PrimRel.AccountRelationship = 'Primary'
   ) AS AccountCustomers
LEFT OUTER JOIN TableThree AS Prim
   ON AccountCustomers.PrimaryAccountCustomer = Prim.AccountCustomer
LEFT OUTER JOIN TableThree AS Sec1
   ON AccountCustomers.1stSecondaryAccountCustomer = Sec1.AccountCustomer
LEFT OUTER JOIN TableThree AS Sec2
   ON AccountCustomers.2ndSecondaryAccountCustomer = Sec2.AccountCustomer
0
 

Author Comment

by:Sue_W
ID: 16708939
That works. I can't use a function but it gives me some ideas to try.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 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