Returning multiple rows from 1 of the 6 tables as columns

Question

Hello, I have a database in which we hold user and emergency contact information.   The tricky part for me is we would like to return the emergency contact results in a column next to the original user.  Listed as [primary contact], [secondary contact].  Using SQL 2005 SP4

select 
empPers.eepNameFirst as [First Name], 
empPers.eepNameLast as [Last Name],
jobCode.jbcDesc as [Job], 
empPers.eepAddressEmail as [Email Address], 
orgLevel.orgDesc as [Department], 
location.locDesc as [Location], 
'('+STUFF(STUFF(empComp.EecPhoneBusinessNumber,7,0,'-'),4,0,') ') AS [Work Phone],
'('+STUFF(STUFF(empPers.eepPhoneHomeNumber,7,0,'-'),4,0,') ') AS [Home Phone],
     
CASE empMPhon.efoPhoneType
	WHEN 'CEL'
	THEN '('+STUFF(STUFF(empMPhon.efoPhoneNumber,7,0,'- '),4,0,') ')
ELSE NULL
END[Mobile Phone]
,
case contacts.conIsEmergencyContact
when 'Y'
then contacts.conNameLast+', '+contacts.conNameFirst
else NULL
end [Emergency Contact]
,     
case
when len(contacts.conPhoneHomeNumber)>0
THEN '('+STUFF(STUFF(contacts.conPhoneHomeNumber,7,0,'- '),4,0,') ')
else null
end [Contact Home Phone]
,     
case 
when len(contacts.conWorkNumber)>0
THEN '('+STUFF(STUFF(contacts.conWorkNumber,7,0,'- '),4,0,') ')
else null
end [Contact Work Phone]
,     
case contacts.conPhoneOtherType
when 'CEL'
THEN '('+STUFF(STUFF(contacts.conPhoneOtherType,7,0,'- '),4,0,') ')
else NULL
end [Contact Mobile Phone]
,
case 
when contacts.conRelationship in ('SPS','PRT') then 'Spouse'
when contacts.conRelationship in ('AUN','BRO','CHL','COU','DAD','FIL','GRD','LGD','MIL','MOM',
'SIS','STC','STF','STM','UNC') then 'Family'
else 'Friend'
end [Contact Relationship]
FROM empPers inner join empComp on eepEEID=eecEEID 
inner join jobCode on empComp.eecJobcode=jobCode.jbcJobCode 
inner join orgLevel on empComp.eecOrgLvl2=orgLevel.orgcode 
inner join location on empComp.eecLocation=location.locCode 
inner join contacts on empPers.eepEEID=contacts.conEEID
left outer join empMPhon on empComp.eecEEID=empMPhon.eFoEEID 
where empComp.eecEmplStatus<>'T' and contacts.conIsEmergencyContact='y'
order by empPers.eepNameLast, empPers.eepNameFirst, empMPhon.efoPhoneNumber desc

This is what I am currently getting:

First   Name

Last   Name

Job

Email   Address

Department

Location

Work   Phone

Home   Phone

Mobile   Phone

Emergency   Contact

Contact   Home Phone

Contact   Work Phone

Contact   Mobile Phone

Contact   Relationship

John

Doe

Admin

john.doe@.com

HR

US

(555)-555-5555

(555)-555-5555

(555)-555-5555

Jane Doe

(555)-555-5555

(555)-555-5555

(555)-555-5555

Spouse

John

Doe

Admin

john.doe@.com

HR

US

(555)-555-5555

(555)-555-5555

(555)-555-5555

Sheila Doe

(555)-555-0123

(555)-555-0124

(555)-555-0125

Sister

This is what I would like to get:

First   Name

Last   Name

Job   Title

Email

Department

Location

Work   Phone

Home   Phone

Mobile   Phone

Primary   Emergency Contact

Primary   Emergency Home Phone

Primary   Emergency Work Phone

Primary   Emergency Mobile Phone

Primary   Emergency Relationship

Secondary   Emergency Contact

Secondary   Emergency Contact Home Phone

Secondary   Emergency Contact Work Phone

Secondary   Emergency Contact Cell Phone

Secondary   Emergency Relationship

John

Doe

Admin

john.doe@.com

HR

US

(555)-555-5555

(555)-555-5555

(555)-555-5555

Jane Doe

(555)-555-5555

(555)-555-5555

(555)-555-5555

Spouse

Sheila Doe

(555)-555-0123

(555)-555-0124

(555)-555-0125

Sister

Answer 1

Have you tried the concept of Pivot table in SQL to generate the result?

Answer 2

Does a column in the contacts table designate whether a contact is primary or secondary?  If so, join to the contacts table twice as in the example below.  If that designation isn't stored, you could add one using row_number and include that in the join clause.  You could explore one of those approaches as an option.

with employees (employee_id, employee_nm) as (
	select 1, 'Frank' union
	select 2, 'Joe'
), contacts (contact_id, employee_id, contact_nm, contact_type) as (
	select 1, 1, 'Milly', 'primary' union
	select 2, 1, 'Heather', 'secondary' union
	select 3, 2, 'Gloria', 'primary'
)


select 
	e.employee_id, e.employee_nm,
	cp.contact_nm, cp.contact_type,
	cs.contact_nm, cs.contact_type
from employees e
left join contacts cp
	on e.employee_id = cp.employee_id and cp.contact_type = 'primary'
left join contacts cs
	on e.employee_id = cs.employee_id and cs.contact_type = 'secondary'
order by e.employee_id

---

website: jontav.com | Blog

Answer 3

To PChiragS:

Yes I have, but I couldn't figure out the syntax.   Perhaps it needs to be nested after the select of the previous columns.  If anyone could help with that syntax, I would be happy to go that route.

Answer 4

To Jon Tavernier:

No they do not specify whether they are primary or secondary.   To make it more confusing there may be 1, 2, or 3 emergency contacts.   Specified with the flag "contacts.conIsEmergencyContact='y'" The first row returned should be primary, the second row should be secondary, and third and subsequent rows should be ignored.

Thanks for your input.

Answer 5

This seems to be typical multi-column pivot which is solved using CASE statements. Take a look at this blog post explaining the concept

Understanding SQL Server 2000 Pivot with Aggregates

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Please go through these given links.

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

these links shows the PIVOT and UNPIVOTING concepts with example.

---

Regards, Chirag Patel (ETL Engineer @C-S-America)

Answer 7

The first row returned should be primary, the second row should be secondary, and third and subsequent rows should be ignored.

Perfect.  Only two rows should be returned and you state that only the first and second should be used.  How are you determining the order in which the contact rows are returned?  A case statement exists to distinguish between "spouse" and "family."  Perhaps you could propose that as the logic for the emergency contacts that will be returned?  "Any two contacts listed as spouse will be selected.  If no spouse exists, any two family contacts will be selected.  If more than two contacts of a given type exist, the emergency contact records chosen will be random within that group."

The example listed below uses a couple sample tables with a simple ranking algorithm of prioritizing spouse above any other relation.  Ideally, you'd have all relations, the groups in which they belong, and possibly their priority ranking stored.

-- sample data
with employees (employee_id, employee_nm) as (
	select 1, 'Frank' union
	select 2, 'Joe' union
	select 3, 'Bob'
), contacts (contact_id, employee_id, contact_nm, relation) as (
	-- frank's contacts
	select 1, 1, 'Milly', 'spouse' union
	select 2, 1, 'Heather', 'family' union
	select 3, 1, 'Bobby', 'family' union
	
	-- joe's contacts
	select 4, 2, 'Sarah', 'spouse' union
	
	-- bob's contacts
	select 5, 3, 'Gene', 'family' union
	select 5, 3, 'Howie', 'family' union
	select 5, 3, 'Steve', 'family'
), ranked_contacts as (
	-- add a rank based on relation type prioritizing spouse over any other relation
	select 
	 c.employee_id, c.contact_nm, c.relation, row_number() over(partition by c.employee_id order by case when c.relation = 'spouse' then 1 else 2 end) as rn
	from contacts c
)


select 
	e.employee_id, e.employee_nm,
	pc.contact_nm as primary_contact_nm, pc.relation as primary_relation,
	sc.contact_nm as secondary_contact_nm, sc.relation as secondary_relation
from employees e
left join ranked_contacts pc	-- join to ranked contacts getting the person ranked highest
	on e.employee_id = pc.employee_id and pc.rn = 1
left join ranked_contacts sc	-- join to ranked contacts getting the person ranked second highest
	on e.employee_id = sc.employee_id and sc.rn = 2

The point I'm attempting to make is that if you know two records need to be selected from the emergency contacts table, define the logic that determines what two records will be chosen (maybe there is none and any two will do), rank the records based on that logic, and perform a couple joins.  It's just one approach to consider as I'm sure there are other solutions. 

---

website: jontav.com | Blog

Answer 8

This seems to be typical multi-column pivot which is solved using CASE statements. Take a look at this blog post explaining the concept

Understanding SQL Server 2000 Pivot with Aggregates

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

SELECT  eepEEID ,
        MIN(CASE WHEN eepEEID = eepEEID THEN empPers.eepNameFirst
            END) AS [First Name] ,
        MIN(CASE WHEN eepEEID = eepEEID THEN empPers.eepNameLast
            END) AS [Last Name] ,
        MIN(CASE WHEN eepEEID = eepEEID THEN jobCode.jbcDesc
            END) AS [Job] ,
        MIN(CASE WHEN eepEEID = eepEEID THEN empPers.eepAddressEmail
            END) AS [Email Address] ,
        MIN(CASE WHEN eepEEID = eepEEID THEN orgLevel.orgDesc
            END) AS [Department] ,
        MIN(CASE WHEN eepEEID = eepEEID THEN location.locDesc
            END) AS [Location] ,
        MIN(CASE WHEN eepEEID = eepEEID
                 THEN '(' + STUFF(STUFF(empComp.EecPhoneBusinessNumber, 7, 0,
                                        '-'), 4, 0, ') ')
            END) AS [Work Phone] ,
        MIN(CASE WHEN eepEEID = eepEEID
                 THEN '(' + STUFF(STUFF(empPers.eepPhoneHomeNumber, 7, 0, '-'),
                                  4, 0, ') ')
            END) AS [Home Phone] ,
        MIN(CASE WHEN empMPhon.efoPhoneType = 'CEL'
                 THEN '(' + STUFF(STUFF(empMPhon.efoPhoneNumber, 7, 0, '- '),
                                  4, 0, ') ')
            END) AS [Mobile Phone] ,

-- Begin Primary emergency contact
        MAX(CASE WHEN eepEEID = eecEEID THEN contacts.conNameFirst
            END) AS [Primary Emergency First Name] ,
        MAX(CASE WHEN eepEEID = eecEEID THEN contacts.conNameLast
            END) AS [Primary Emergency Last Name] ,
        MAX(CASE WHEN contacts.conPhoneHomeNumber IS NULL THEN 'NULL'
                 ELSE '(' + STUFF(STUFF(contacts.conPhoneHomeNumber, 7, 0,
                                        '- '), 4, 0, ') ')
            END) AS [Primary Emergency Home Phone] ,
        MAX(CASE WHEN contacts.conWorkNumber IS NULL THEN 'NULL'
                 ELSE '(' + STUFF(STUFF(contacts.conWorkNumber, 7, 0, '- '), 4,
                                  0, ') ')
            END) AS [Primary Emergency Work Phone] ,
        MAX(CASE WHEN contacts.conPhoneOtherType = 'CEL'
                 THEN '(' + STUFF(STUFF(contacts.conPhoneOtherNumber, 7, 0,
                                        '- '), 4, 0, ') ')
                 ELSE NULL
            END) AS [Primary Emergency Mobile Phone] ,
        MAX(CASE WHEN contacts.conRelationship IN ( 'SPS', 'PRT' )
                 THEN 'Spouse'
                 WHEN contacts.conRelationship IN ( 'AUN', 'BRO', 'CHL', 'COU',
                                                    'DAD', 'FIL', 'GRD', 'LGD',
                                                    'MIL', 'MOM', 'SIS', 'STC',
                                                    'STF', 'STM', 'UNC' )
                 THEN 'Family'
                 ELSE 'Friend'
            END) AS [Contact Relationship] ,
--Begin Secondary emergency contact	
        MIN(CASE WHEN eepEEID = eecEEID THEN contacts.conNameFirst
            END) AS [Secondary Emergency First Name] ,
        MIN(CASE WHEN eepEEID = eecEEID THEN contacts.conNameLast
            END) AS [Secondary Emergency Last Name] ,
        MIN(CASE WHEN contacts.conPhoneHomeNumber IS NULL THEN 'NULL'
                 ELSE '(' + STUFF(STUFF(contacts.conPhoneHomeNumber, 7, 0,
                                        '- '), 4, 0, ') ')
            END) AS [Secondary Emergency Home Phone] ,
        MIN(CASE WHEN contacts.conWorkNumber IS NULL THEN 'NULL'
                 ELSE '(' + STUFF(STUFF(contacts.conWorkNumber, 7, 0, '- '), 4,
                                  0, ') ')
            END) AS [Secondary Emergency Work Phone] ,
        MIN(CASE WHEN contacts.conPhoneOtherType = 'CEL'
                 THEN '(' + STUFF(STUFF(contacts.conPhoneOtherNumber, 7, 0,
                                        '- '), 4, 0, ') ')
                 ELSE NULL
            END) AS [Secondary Emergency Mobile Phone] ,
        MIN(CASE WHEN contacts.conRelationship IN ( 'SPS', 'PRT' )
                 THEN 'Spouse'
                 WHEN contacts.conRelationship IN ( 'AUN', 'BRO', 'CHL', 'COU',
                                                    'DAD', 'FIL', 'GRD', 'LGD',
                                                    'MIL', 'MOM', 'SIS', 'STC',
                                                    'STF', 'STM', 'UNC' )
                 THEN 'Family'
                 ELSE 'Friend'
            END) AS [Secondary Contact Relationship]
	


FROM    empPers
        INNER JOIN empComp ON eepEEID = eecEEID
        INNER JOIN jobCode ON empComp.eecJobcode = jobCode.jbcJobCode
        INNER JOIN orgLevel ON empComp.eecOrgLvl2 = orgLevel.orgcode
        INNER JOIN location ON empComp.eecLocation = location.locCode
        INNER JOIN contacts ON empPers.eepEEID = contacts.conEEID
        LEFT OUTER JOIN empMPhon ON empComp.eecEEID = empMPhon.eFoEEID
WHERE   empComp.eecEmplStatus <> 'T'
        AND contacts.conIsEmergencyContact = 'y'
		AND contacts.ConNameFirst <> 'INACTIVE%' 
GROUP BY eepEEID
ORDER BY [First Name] ASC

The problem I am having now is the phone numbers are not being displayed correctly.   Likely because the aggregate is taking the min, or maximum phone number value instead of keeping it associated with the emergency contact.   Can anyone think of a logical way to keep them linked?

Thanks,