Combining multiple records into one using SQL

This is an example of how I did it for the PAEMPPOS info. You could do the same thing with the contact table using the SEQ_NBR instead of the pos_level. We have a maximum of 5 positions per employee so I've done 5 sections. You would need to determine your maximum number of emergency contacts. I'm sure there is a sharper way to do it, my SQL skills are limited! Hope this helps.

-- Pull all active positions by employee into one row.
select
employee
--Pos 1
,max(case when pos_level = '1' then POSITION end) Pos1
,max(case when pos_level = '1' then POS_LEVEL end) PosLvl1
,max(case when pos_level = '1' then FTE end) FTE1
,max(case when pos_level = '1' then EFFECT_DATE end) EffDt1
,max(case when pos_level = '1' then POS_EFF_DT end) PosEffDt1
,max(case when pos_level = '1' then JOB_CODE end) Job1
,max(case when pos_level = '1' then PROCESS_LEVEL end) PL1
,max(case when pos_level = '1' then DEPARTMENT end) Dept1
,max(case when pos_level = '1' then EXP_ACCT_UNIT end) AcctUnit1
,max(case when pos_level = '1' then SCHEDULE end) Sched1
,max(case when pos_level = '1' then LOCAT_CODE end) Loc1
,max(case when pos_level = '1' then WORK_SCHED end) Work_Sched1
,max(case when pos_level = '1' then USER_LEVEL end) UL1
,max(case when pos_level = '1' then SUPERVISOR end) Supv1
,max(case when pos_level = '1' then SUPERVISOR_IND end) Supv_Ind1
,max(case when pos_level = '1' then PAY_RATE end) Rate1
,max(case when pos_level = '1' then PAY_GRADE end) Grade1
,max(case when pos_level = '1' then PAY_STEP end) Step1
,max(case when pos_level = '1' then ANNUAL_HOURS end) Hours1
--Pos2
,max(case when pos_level = '2' then POSITION end) Pos2
,max(case when pos_level = '2' then POS_LEVEL end) PosLvl2
,max(case when pos_level = '2' then FTE end) FTE2
,max(case when pos_level = '2' then EFFECT_DATE end) EffDt2
,max(case when pos_level = '2' then POS_EFF_DT end) PosEffDt2
,max(case when pos_level = '2' then JOB_CODE end) Job2
,max(case when pos_level = '2' then PROCESS_LEVEL end) PL2
,max(case when pos_level = '2' then DEPARTMENT end) Dept2
,max(case when pos_level = '2' then EXP_ACCT_UNIT end) AcctUnit2
,max(case when pos_level = '2' then SCHEDULE end) Sched2
,max(case when pos_level = '2' then LOCAT_CODE end) Loc2
,max(case when pos_level = '2' then WORK_SCHED end) Work_Sched2
,max(case when pos_level = '2' then USER_LEVEL end) UL2
,max(case when pos_level = '2' then SUPERVISOR end) Supv2
,max(case when pos_level = '2' then SUPERVISOR_IND end) Supv_Ind2
,max(case when pos_level = '2' then PAY_RATE end) Rate2
,max(case when pos_level = '2' then PAY_GRADE end) Grade2
,max(case when pos_level = '2' then PAY_STEP end) Step2
,max(case when pos_level = '2' then ANNUAL_HOURS end) Hours2
----Pos3
,max(case when pos_level = '3' then POSITION end) Pos3
,max(case when pos_level = '3' then POS_LEVEL end) PosLvl3
,max(case when pos_level = '3' then FTE end) FTE3
,max(case when pos_level = '3' then EFFECT_DATE end) EffDt3
,max(case when pos_level = '3' then POS_EFF_DT end) PosEffDt3
,max(case when pos_level = '3' then JOB_CODE end) Job3
,max(case when pos_level = '3' then PROCESS_LEVEL end) PL3
,max(case when pos_level = '3' then DEPARTMENT end) Dept3
,max(case when pos_level = '3' then EXP_ACCT_UNIT end) AcctUnit3
,max(case when pos_level = '3' then SCHEDULE end) Sched3
,max(case when pos_level = '3' then LOCAT_CODE end) Loc3
,max(case when pos_level = '3' then WORK_SCHED end) Work_Sched3
,max(case when pos_level = '3' then USER_LEVEL end) UL3
,max(case when pos_level = '3' then SUPERVISOR end) Supv3
,max(case when pos_level = '3' then SUPERVISOR_IND end) Supv_Ind3
,max(case when pos_level = '3' then PAY_RATE end) Rate3
,max(case when pos_level = '3' then PAY_GRADE end) Grade3
,max(case when pos_level = '3' then PAY_STEP end) Step3
,max(case when pos_level = '3' then ANNUAL_HOURS end) Hours3
--Pos4
,max(case when pos_level = '4' then POSITION end) Pos4
,max(case when pos_level = '4' then POS_LEVEL end) PosLvl4
,max(case when pos_level = '4' then FTE end) FTE4
,max(case when pos_level = '4' then EFFECT_DATE end) EffDt4
,max(case when pos_level = '4' then POS_EFF_DT end) PosEffDt4
,max(case when pos_level = '4' then JOB_CODE end) Job4
,max(case when pos_level = '4' then PROCESS_LEVEL end) PL4
,max(case when pos_level = '4' then DEPARTMENT end) Dept4
,max(case when pos_level = '4' then EXP_ACCT_UNIT end) AcctUnit4
,max(case when pos_level = '4' then SCHEDULE end) Sched4
,max(case when pos_level = '4' then LOCAT_CODE end) Loc4
,max(case when pos_level = '4' then WORK_SCHED end) Work_Sched4
,max(case when pos_level = '4' then USER_LEVEL end) UL4
,max(case when pos_level = '4' then SUPERVISOR end) Supv4
,max(case when pos_level = '4' then SUPERVISOR_IND end) Supv_Ind4
,max(case when pos_level = '4' then PAY_RATE end) Rate4
,max(case when pos_level = '4' then PAY_GRADE end) Grade4
,max(case when pos_level = '4' then PAY_STEP end) Step4
,max(case when pos_level = '4' then ANNUAL_HOURS end) Hours4
--Pos5
,max(case when pos_level = '5' then POSITION end) Pos5
,max(case when pos_level = '5' then POS_LEVEL end) PosLvl5
,max(case when pos_level = '5' then FTE end) FTE5
,max(case when pos_level = '5' then EFFECT_DATE end) EffDt5
,max(case when pos_level = '5' then POS_EFF_DT end) PosEffDt5
,max(case when pos_level = '5' then JOB_CODE end) Job5
,max(case when pos_level = '5' then PROCESS_LEVEL end) PL5
,max(case when pos_level = '5' then DEPARTMENT end) Dept5
,max(case when pos_level = '5' then EXP_ACCT_UNIT end) AcctUnit5
,max(case when pos_level = '5' then SCHEDULE end) Sched5
,max(case when pos_level = '5' then LOCAT_CODE end) Loc5
,max(case when pos_level = '5' then WORK_SCHED end) Work_Sched5
,max(case when pos_level = '5' then USER_LEVEL end) UL5
,max(case when pos_level = '5' then SUPERVISOR end) Supv5
,max(case when pos_level = '5' then SUPERVISOR_IND end) Supv_Ind5
,max(case when pos_level = '5' then PAY_RATE end) Rate5
,max(case when pos_level = '5' then PAY_GRADE end) Grade5
,max(case when pos_level = '5' then PAY_STEP end) Step5
,max(case when pos_level = '5' then ANNUAL_HOURS end) Hours5
from paemppos PA where
end_date = TO_DATE ('01-JAN-1700', 'DD-MON-YYYY')
group by
employee

Thanks very much, Miki. My SQL skills are also limited. I didn't want to make my original note too long so I left out the fact that I've used your method but, unfortunately, because of the way the seq_nbr is utilized in paemergcnt, it's ugly. It looks like the seq_nbr keeps incrementing so that I have one employee, for example, whose records use up seq_nbr 4, 6, 8 and 9 (no doubt due to changes/additions/deletions of contacts over time). So, I have this going up to 10 contacts so these are covered. It ends up being long and ugly so that what I end up sending to the other system is 10 instances of emergency contacts with, for most, only 1 used. I was thinking there must be a better way. Thanks again for your input.

Here is an idea on how to do it. I have it do up to three but you may take it as far as you want.

SELECT LTRIM(RTRIM(c1.FIRST_NAME)) + ' ' + LTRIM(RTRIM(c1.LAST_NAME)) AS FULL_NAME1
, c1.HM_PHONE_NBR
, c1.WK_PHONE_NBR
,LTRIM(RTRIM(c2.FIRST_NAME)) + ' ' + LTRIM(RTRIM(c2.LAST_NAME)) AS FULL_NAME2
, c2.HM_PHONE_NBR
, c2.WK_PHONE_NBR
,LTRIM(RTRIM(c3.FIRST_NAME)) + ' ' + LTRIM(RTRIM(c3.LAST_NAME)) AS FULL_NAME3
, c3.HM_PHONE_NBR
, c3.WK_PHONE_NBR
FROM PAEMERGCNT c1
LEFT OUTER JOIN PAEMERGCNT c2
ON c1.COMPANY = c2.COMPANY
AND c1.EMPLOYEE = c2.EMPLOYEE
AND c1.SEQ_NBR <> c2.SEQ_NBR
AND c2.SEQ_NBR =
(
SELECT MIN(SEQ_NBR)
FROM PAEMERGCNT c_next
WHERE c_next.COMPANY = c2.COMPANY
AND c_next.EMPLOYEE = c2.EMPLOYEE
AND c_next.SEQ_NBR <> c1.SEQ_NBR
)
LEFT OUTER JOIN PAEMERGCNT c3
ON c1.COMPANY = c3.COMPANY
AND c1.EMPLOYEE = c3.EMPLOYEE
AND c1.SEQ_NBR <> c2.SEQ_NBR
AND c2.SEQ_NBR <> c3.SEQ_NBR
AND c3.SEQ_NBR =
(
SELECT MIN(SEQ_NBR)
FROM PAEMERGCNT c_next
WHERE c_next.COMPANY = c2.COMPANY
AND c_next.EMPLOYEE = c2.EMPLOYEE
AND c_next.SEQ_NBR <> c1.SEQ_NBR
AND c_next.SEQ_NBR <> c2.SEQ_NBR
)
WHERE c1.SEQ_NBR =
(
SELECT MIN(SEQ_NBR)
FROM PAEMERGCNT c_min
WHERE c_min.COMPANY = c1.COMPANY
AND c_min.EMPLOYEE = c1.EMPLOYEE
)

Depending on your backend db, there are various ways in SQL to create this using pivots. =