How can I pull several rows from 1 table and create multiple columns in a join?

I have 3 tables I am working with: Customers, Loans and References.  I can do a join of Customers and Loans based on the CustomerId to get the info I want so far.

LNAME, FNAME, LOAN AMT, ETC

Now, there is a table called "references"  This table has 1-6 references per Customer.  Each reference takes 1 row in the table.  I want to end up with:

LNAME, FNAME, LOAN AMT, ETC, REF1NAME, REF1PHONE, REF2NAME, REF2PHONE, REF3NAME, REF3PHONE and so on.

Can this be done with a looping query, nested query or what?
roarteamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Allan ReitanChief Financial OfficerCommented:
If you wanted to attempt the the nested query method, my recommended strategy would be to create a Pivot Result Set of your references table to first establish single row record record set for your customer references.

Something like this:
WITH A AS
(
  SELECT ROW(CustID) OVER (PARTITION BY CustID) AS RowCount
  ,CustID
  ,Name
  ,Phone
)
SELECT A.CustID
  ,CASE A.RowCount WHEN 1 THEN A.Name ELSE NULL END AS REF1NAME
  ,CASE A.RowCount WHEN 1 THEN A.Phone ELSE NULL END AS REF1PHONE
  ,CASE A.RowCount WHEN 2 THEN A.Name ELSE NULL END AS REF1NAME
  ,CASE A.RowCount WHEN 2 THEN A.Phone ELSE NULL END AS REF1PHONE
  , <...and So on, and so on..>
  ,CASE A.RowCount WHEN 6 THEN A.Name ELSE NULL END AS REF6NAME
  ,CASE A.RowCount WHEN 6 THEN A.Phone ELSE NULL END AS REF6PHONE
FROM A
GROUP BY A.CustID;

if you can access the Database Server and actually save this up there as a view, then you could just do an INNER JOIN on this new view, VIA CustID with your currently established Query that generates your other known fields.
0
PortletPaulfreelancerCommented:
I think the intention is to use ROW_NUMBER()
and use MAX() on each of the case expressions

You can do this using a CTE (as used above) or as a derived table (see below):
select
*
from Customers C
inner join Loans L on C.CustID = L.CustID
left join (
          SELECT
                A.CustID
              , MAX(CASE WHEN A.rn = 1 THEN A.Name  END) AS REF1NAME
              , MAX(CASE WHEN A.rn = 1 THEN A.Phone END) AS REF1PHONE
              , MAX(CASE WHEN A.rn = 2 THEN A.Name  END) AS REF1NAME
              , MAX(CASE WHEN A.rn = 2 THEN A.Phone END) AS REF1PHONE
              
                -- etc...
              
              , MAX(CASE WHEN A.rn = 6 THEN A.Name  END) AS REF6NAME
              , MAX(CASE WHEN A.rn = 6 THEN A.Phone END) AS REF6PHONE
          FROM (
                SELECT
                      ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY [somefield]) AS rn
                    , CustID
                    , Name
                    , Phone
                FROM References) A
          GROUP BY A.CustID
          ) R on C.CustID = R.CustID
;

Open in new window

0
roarteamAuthor Commented:
Thanks for the responses.  So far, I have used:

select
   LNAME, REF1NAME, REF1PHONE, REF2NAME, REF2PHONE
from Customers C
inner join PayDayLoans P on C.customerid = P.customerid
left join (
          SELECT
                A.customerid
              , MAX(CASE WHEN A.rn = 1 THEN A.referencename  END) AS REF1NAME
              , MAX(CASE WHEN A.rn = 1 THEN A.Phone1 END) AS REF1PHONE
              , MAX(CASE WHEN A.rn = 2 THEN A.referencename  END) AS REF2NAME
              , MAX(CASE WHEN A.rn = 2 THEN A.Phone1 END) AS REF2PHONE            
              , MAX(CASE WHEN A.rn = 3 THEN A.referencename  END) AS REF3NAME
              , MAX(CASE WHEN A.rn = 3 THEN A.Phone1 END) AS REF3PHONE
          FROM (
                SELECT
                      ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY referenceid) AS rn
                    , customerid
                    , referencename
                    , phone1
                FROM customerreferences) A
          GROUP BY A.customerid
          ) R on C.customerid = R.customerid
;

This gives:
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
HAWKINS      CRAIG GLANZE      205362XXXX      TINA GLANZE      205863XXXX
FULLER      APRIL ST JOHN      205500XXXX      GEWN FULLER      205914XXXX
FULLER      APRIL ST JOHN      205500XXXX      GEWN FULLER      205500XXXX      
FULLER      APRIL ST JOHN      205500XXXX      GEWN FULLER      205500XXXX      
FULLER      APRIL ST JOHN      205500XXXX      GEWN FULLER      205500XXXX      
FULLER      APRIL ST JOHN      205500XXXX      GEWN FULLER      205500XXXX      
FULLER      APRIL ST JOHN      205500XXXX      GEWN FULLER      205500XXXX      
DENTON      BECKY RICHEY      205999XXXX      LYNN MARNEY      205405XXXX

So close.  How do i remove the duplicates?  I have tried "GROUP BY C.CUSTOMERID", etc, but keep getting error messages like
     "Column 'Customers.LNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

 I can specifically use "GROUP BY LNAME, REF1NAME, REF1PHONE, REF2NAME, REF2PHONE" and it works, but by the time I include 20 columns, it would be a mess.  Theres got to be an easier way...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Allan ReitanChief Financial OfficerCommented:
You have two options that you try:

1. At the start of your T-SQL use DISTINCT:

SELECT DISTINCT
   LNAME, REF1NAME, REF1PHONE, REF2NAME, REF2PHONE
from Customers C ... etc.

2. In the Group By just use integers for column numbers in the output set:

GROUP BY 1,2,3,4,5,6,7,8,....,22, etc.

Still kind of tedious because as you point out if you drop in another set of columns you have to go expand out the GROUP BY, whereas SELECT DISTINCT will already force the output set to reduce duplicates.

The drawback is that DISTINCT runs into some challenges with scalability.  If you need speed and performance, DISTINCT will break down because the database has to perform a whole second pass over the result set to reduce the duplicates.
0
PortletPaulfreelancerCommented:
If you literally ONLY wanted some customer detail plus references, then do NOT join to Loans
select
    c.LNAME, r.REF1NAME, r.REF1PHONE, r.REF2NAME, r.REF2PHONE
from Customers C
left join (
          SELECT
                A.CustID
              , MAX(CASE WHEN A.rn = 1 THEN A.Name  END) AS REF1NAME
              , MAX(CASE WHEN A.rn = 1 THEN A.Phone END) AS REF1PHONE
              , MAX(CASE WHEN A.rn = 2 THEN A.Name  END) AS REF1NAME
              , MAX(CASE WHEN A.rn = 2 THEN A.Phone END) AS REF1PHONE
              
                -- etc...
              
              , MAX(CASE WHEN A.rn = 6 THEN A.Name  END) AS REF6NAME
              , MAX(CASE WHEN A.rn = 6 THEN A.Phone END) AS REF6PHONE
          FROM (
                SELECT
                      ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY [somefield]) AS rn
                    , CustID
                    , Name
                    , Phone
                FROM References) A
          GROUP BY A.CustID
          ) R on C.CustID = R.CustID
;

Open in new window


If you do need some Loans table information place the join of customers to loans into a subquery, grouping just the fields you need, e.g.
select
    cl.LNAME, cl.[LOAN AMT], r.REF1NAME, r.REF1PHONE, r.REF2NAME, r.REF2PHONE
from (
      select
           C.CustID, c.LNAME, MAX(l.[LOAN AMT]) as [LOAN AMT]
      Customers C
      inner join Loans L on C.CustID = L.CustID
      group by
            C.CustID, c.LNAME
      ) cl
left join (
          SELECT
                A.CustID
              , MAX(CASE WHEN A.rn = 1 THEN A.Name  END) AS REF1NAME
              , MAX(CASE WHEN A.rn = 1 THEN A.Phone END) AS REF1PHONE
              , MAX(CASE WHEN A.rn = 2 THEN A.Name  END) AS REF1NAME
              , MAX(CASE WHEN A.rn = 2 THEN A.Phone END) AS REF1PHONE
              
                -- etc...
              
              , MAX(CASE WHEN A.rn = 6 THEN A.Name  END) AS REF6NAME
              , MAX(CASE WHEN A.rn = 6 THEN A.Phone END) AS REF6PHONE
          FROM (
                SELECT
                      ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY [somefield]) AS rn
                    , CustID
                    , Name
                    , Phone
                FROM References) A
          GROUP BY A.CustID
          ) R on CL.CustID = R.CustID
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
roarteamAuthor Commented:
Thanks for all the help!  The LOANS table WAS causing the duplicates.  Once we filtered for a certain status, it gave us exactly what we needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.