CODEWITHSUNDEEP
sqlsql-serverselectgroup-byleft-join
JavaDeveloper
JavaDeveloper

Reputation: 39

How to combine different rows in SQL Server?

Query:

select 
    ag.AGENTID as AGENTID, ag.AGENTNAME as AGENTNAME,
    case when pc.CONTACTTYPECD='M' then pc.CONTACTNUM end as 'MOBILE',
    case when pc.CONTACTTYPECD='R' then pc.CONTACTNUM end as 'RESIDENCE',
    case when pc.CONTACTTYPECD='EM' then pc.CONTACTNUM end as 'EMERGENCY_NO'
from 
    AGENT ag 
left join 
    PARTY p on p.PARTYID = ag.PARTYID
left join  
    PARTYCONTACT pc on pc.PARTYSEQ = p.PARTYSEQ
where 
    ag.AGENTID = '10000005'

Output

enter image description here

Expected Output
enter image description here

Upvotes: 2

Views: 61

Answers (2)

unfinishedmonkey
unfinishedmonkey

Reputation: 76

or join to each type of contact seperately like this...

  SELECT ag.AGENTID AS AGENTID, ag.AGENTNAME AS AGENTNAME,
           MobileContact.CONTACTNUM AS 'MOBILE',
           ResidenceContact.CONTACTNUM AS 'RESIDENCE',
           EmergencyContact.CONTACTNUM AS 'EMERGENCY_NO'
    FROM AGENT ag 

    LEFT JOIN PARTY p ON p.PARTYID = ag.PARTYID

    LEFT JOIN PARTYCONTACT as MobileContact 
    ON MobileContact.PARTYSEQ = p.PARTYSEQ
    AND MobileContact.CONTACTTYPECD='M'

    LEFT JOIN PARTYCONTACT as ResidenceContact 
    ON ResidenceContact.PARTYSEQ = p.PARTYSEQ
    AND ResidenceContact.CONTACTTYPECD='R'

    LEFT JOIN PARTYCONTACT as EmergencyContact 
    ON EmergencyContact.PARTYSEQ = p.PARTYSEQ
    AND EmergencyContact.CONTACTTYPECD='EM'

    WHERE ag.AGENTID = '10000005'

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT ag.AGENTID AS AGENTID, ag.AGENTNAME AS AGENTNAME,
       MAX(CASE WHEN pc.CONTACTTYPECD='M' THEN pc.CONTACTNUM END) AS 'MOBILE',
       MAX(CASE WHEN pc.CONTACTTYPECD='R' THEN pc.CONTACTNUM END) AS 'RESIDENCE',
       MAX(CASE WHEN pc.CONTACTTYPECD='EM' THEN pc.CONTACTNUM END) AS 'EMERGENCY_NO'
FROM AGENT ag 
LEFT JOIN PARTY p ON p.PARTYID = ag.PARTYID
LEFT JOIN PARTYCONTACT pc ON pc.PARTYSEQ = p.PARTYSEQ
WHERE ag.AGENTID = '10000005'
GROUP BY ag.AGENTID, ag.AGENTNAME

Upvotes: 1

Related Questions