CODEWITHSUNDEEP
sqlsql-servert-sql
Jammer
Jammer

Reputation: 2410

SQL insert multiple rows from one table into columns on another table

We have one table of company details and another table of contacts linked to these companies, so there can be many contact per company. Someone is going to go through the contacts table and reduce the contacts per company to 2.

They then need to create a table of company details with each contact from the contacts table added on the end in columns e.g. [Contact1Name], [Contact1Tel], [Contact2Name], [Contact2Tel].

So that they have all the information in one table/spreadsheet, is there a way to do this automatically via tsql or will this have to be done manually?

Each contact is linked to a company via a company id column.

Thanks for any help.

Upvotes: 3

Views: 3277

Answers (3)

RGO
RGO

Reputation: 4727

I come from Oracle background; but I like this beautiful solution.

Order the records in CONTACTS by COMPANY_ID, assign each record a unique number, find the minimum and maximum number given to each company, and then, select their two corresponding records:

WITH T AS
(
  SELECT ROWNUM RN, C.* FROM
          (SELECT CONTACTS.COMPANY_ID, 
                  COMPANY.COMPANY_NAME, 
                  CONTACTS.NAME, 
                  CONTACTS.TEL
           FROM CONTACTS, COMPANY 
           WHERE CONTACTS.COMPANY_ID = COMPANY.COMPANY_ID
           ORDER BY CONTACTS.COMPANY_ID ) C
), R AS 
(
  SELECT COMPANY_NAME, MIN(RN) MINR, MAX(RN) MAXR FROM T 
  GROUP BY COMPANY_NAME
)
SELECT R.COMPANY_NAME, T1.NAME NAME1, T1.TEL TEL1, 
                 T2.NAME NAME2,  T2.TEL TEL2
FROM R, T T1, T T2
WHERE R.MINR = T1.RN and R.MAXR = T2.RN

Upvotes: 1

Andomar
Andomar

Reputation: 238076

What you're doing is called denormalization. That can be a good thing (it certainly makes writing queries easier.)

One way is to assign a row number to each contact. You can then extract the first and the second contact:

; with  (
        select  row_number() over (partition by CompanyID 
                                   order by ContactName) as rn
        ,       *
        from    OldTable
        ) as numbered_contacts
insert  NewTable
        (CompanyID, ContactName1, ContactTel1, ContactName2, ContactTel2)
select  c1.CompanyID
,       c1.ContactName as ContactName1
,       c1.ContactTel as ContactTel1
,       c2.ContactName as ContactName2
,       c2.ContactTel as ContactTel2
from    numbered_contacts c1
left join
        numbered_contacts c2
on      c1.CompanyID = c2.CompanyID
        and c2.rn = 2 -- Second contact
where   c1.rn = 1 -- Fist contact

Upvotes: 0

semao
semao

Reputation: 1757

You can extract the data like this:

WITH idList AS
(
    SELECT CompanyID, 
           MIN(ContactID) as id1,
           MAX(ContactId) as id2 
    FROM Contact GROUP BY CompanyID
)
SELECT c.*,c1.*,c2.*
FROM Company c 
INNER JOIN idList ON c.CompanyID = idList.CompanyID
INNER JOIN Contact c1 ON c1.ContactId = idList.id1
INNER JOIN Contact c2 ON c2.ContactId = idList.id2

You will get company data (c) and data of two contacts (c1 and c2)

Upvotes: 1

Related Questions