Extract Data in Single Record

mrfalk

Need to write extract file providing 2 employee emergency contacts with one phone number each in one record. Going to order by emergency contact name when selecting which contact and sort by phone type when selecting phone. I'm at a loss at how to do this and would appreciate any help Fields in Row: EmpNum EmergencyContact1 Relation1 PhType1 Phone1 EmergencyContact2 Relation2 PhType2 Phone2

EmpNum EmpEmergencyContact Relation PhType Phone
123456 Rick Grimes SP Cell 9999999999
123456 Rick Grimes SP Work 8888888888
123456 Daryl Dixon FR Cell 7777777777
123456 Daryl Dixon FR Home 3333333333
123456 Maggie Greene CH Cell 5555555555
123456 Maggie Greene CH Home 6666666666

data modeling guy

SELECT * FROM
(
SELECT EMpNum "EmpNum", EmergencyContact1 "Name", Relation1 "Relation", PhType1 "Phone Type", Phone1 "Phone"
FROM EMERGENCY_CONTACT

UNION

SELECT EMpNum "EmpNum", EmergencyContact2 "Name", Relation2 "Relation", PhType2 "Phone Type", Phone2 "Phone"
FROM EMERGENCY_CONTACT

) EMERGENCY_CONTACTS

ORDER BY "EmpNum", "Name", "Phone Type"

Richard Deeming

Something like this should work:

WITH cteOrderedContacts As
(
SELECT
EmpNum,
EmpEmergencyContact,
Relation,
PhType,
Phone,

    -- Generate a row number for each employee, ordered by contact name:
    ROW\_NUMBER() OVER (PARTITION BY EmpNum ORDER BY EmpEmergencyContact) As RN
FROM
    SourceData

)
SELECT
C1.EmpNum,
C1.EmpEmergencyContact As EmergencyContact1,
C1.Relation As Relation1,
C1.PhType As PhType1,
C1.Phone As Phone1,
C2.EmpEmergencyContact As EmergencyContact2,
C2.Relation As Relation2,
C2.PhType As PhType2,
C2.Phone As Phone2
FROM
cteOrderedContacts As C1
LEFT JOIN cteOrderedContacts As C2
ON C2.EmpNum = C1.EmpNum -- Record must be for the same employee;
And C2.RN = C1.RN + 1 -- Get the next record in the sequence;
WHERE
-- Only return odd-numbered rows, as the even-numbered rows
-- will be included in the previous row:
(C1.RN & 1) = 1
ORDER BY
C1.EmpNum,
C1.RN
;

http://www.sqlfiddle.com/#!3/21e9e/4/0[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

PIEBALDconsult

I should hope that you have translation tables for Relation and PhType -- you can add some sort of prioritization there to better control ordering. RelationshipType ID Abbr Name Priority 1 SP Spouse 1 2 FR Friend 3 3 CH Child 2 PhoneType ID Abbr Name Priority 1 Cell Mobile 1 2 Work Office 3 3 Home Home 2 Such translation table also allow you to use referential integrity and can reduce the amount of space used by the database (not so much a concern these days). Also, it allows your application code to use an enumeration rather than hard-coded string values. Then follow Richard Deeming's advice.

You'll never get very far if all you do is follow instructions.