TSQL – Parent Child (1 to zero/many) Grouping/Aggregation

Code (Sample Data Staging):

DECLARE @Emp TABLE
    (
        [EId] INT IDENTITY(1, 1)
      , [FN]  NVARCHAR(50)
      , [LN]  NVARCHAR(50)
    ) ;
DECLARE @EmpPhCont TABLE
    (
        [EId]       INT
      , [PhType]    VARCHAR(10)
      , [PhNum]     VARCHAR(16)
      , [PhExt]     VARCHAR(10)
      , [IsMain]    BIT
      , [CreatedOn] DATETIME
    ) ;

INSERT INTO @Emp
VALUES
    ( N'Emp1', N'Emp1' )
  , ( N'Emp2', N'Emp2' )
  , ( N'Emp3', N'Emp3' )
  , ( N'Emp4', N'Emp4' )
  , ( N'Emp5', N'Emp5' )
  , ( N'Emp6', N'Emp5' ) ;

INSERT INTO @EmpPhCont
VALUES
    ( 1, 'Home', '111111111', NULL, 0, '2020-01-01 00:00:01' )
  , ( 1, 'Mobile', '222222222', NULL, 1, '2020-01-01 00:00:02' )
  , ( 1, 'Work', '333333333', NULL, 0, '2020-01-01 00:00:03' )

  , ( 2, 'Work', '444444444', '567', 1, '2020-01-01 00:00:04' )
  , ( 2, 'Mobile', '555555555', NULL, 0, '2020-01-01 00:00:05' )
  , ( 2, 'Mobile', '454545454', NULL, 0, '2020-01-01 00:00:06' )

  , ( 3, 'Home', '777777777', NULL, 0, '2020-01-01 00:00:07' )
  , ( 3, 'Mobile', '888888888', NULL, 1, '2020-01-01 00:00:08' )
  , ( 3, 'Mobile', '12121212', NULL, 0, '2020-01-01 00:00:09' )

  , ( 4, 'Work', '101010101', '111', 1, '2020-01-01 00:00:10' )
  , ( 4, 'Work', '101010102', '232', 0, '2020-01-01 00:00:11' )

  , ( 5, 'Work', '545454545', '456', 0, '2020-01-01 00:00:10' )
  , ( 5, 'Work', '456456456', NULL, 1, '2020-01-01 00:00:11' )  ;

Description:

@Emp is the sample Employee table (Unique Employee records).

• EId = Employee Id
• FN = First Name
• LN = Last Name

@EmpPhCont is the sample Employee Phone Contact table (Each Emp from @Emp table can have zero, one, or multiple phone numbers here – unique by Emp/Type).

• PhType = Phone Type (home, mobile, work, and etc)
• PhNum = Phone Number
• PhExt = Phone Extension (mostly available for “Work” PhType)
• IsMain = Is it main contact number. Each employee with a phone num will have exactly 1 record marked as IsMain.
• CreatedOn = Date the record was created

Goal:

To output 1 record per employee with the following Columns

EId | HomeNum | MobileNum | WorkNum | WorkNumExt | MainPhType

Rules:

Return all EId for all records from @Emp, whether they have a @EmpPhCont record or not.

For each emp that has @EmpPhCont record avail, return the newest created PhNum and PhExt for the corresponding PhType, UNLESS an older record for the same Emp/PhType is marked as IsMain = 1 (For any emp, for whichever PhType, if IsMain = 1, always return that PhNum and PhExt value).

Expected Output:

EId HomeNum     MobileNum   WorkNum     WorkNumExt  MainPhType
1   111111111   222222222   333333333   NULL        Mobile
2   NULL        454545454   444444444   567         Work
3   777777777   888888888   NULL        NULL        Mobile
4   NULL        NULL        101010102   111         Work
5   NULL        NULL        456456456   NULL        Work
6   NULL        NULL        NULL        NULL        NULL

My unsuccessful try:

SELECT      [EM].[EId]
          , MAX ( IIF([PH].[PhType] = 'Home', [PH].[PhNum], NULL)) AS [HomePhNum]
          , MAX ( IIF([PH].[PhType] = 'Mobile', [PH].[PhNum], NULL)) AS [MobilePhNum]
          , MAX ( IIF([PH].[PhType] = 'Work', [PH].[PhNum], NULL)) AS [WorkPhNum]
FROM        @Emp AS [EM]
LEFT JOIN   @EmpPhCont AS [PH]
ON          [EM].[EId] = [PH].[EId]
GROUP BY    [EM].[EId] ;

Answer

Use ROW_NUMBER() window function inside a CTE to get the rows from @EmpPhCont that you want returned and join this CTE to @Emp:

with cte as (
  select *,
    row_number() over (partition by [EId], [PhType] order by [IsMain] desc, [CreatedOn] desc) rn
  from @EmpPhCont
)
select e.[EId],
  max(case when c.[PhType] = 'Home' then c.[PhNum] end) HomeNum,
  max(case when c.[PhType] = 'Mobile' then c.[PhNum] end) MobileNum,
  max(case when c.[PhType] = 'Work' then c.[PhNum] end) WorkNum,
  max(case when c.[PhType] = 'Work' then c.[PhExt] end) WorkNumExt,
  max(case when c.[IsMain] = 1 then c.[PhType] end) MainPhType 
from @Emp e left join cte c
on c.[EId] = e.[EId] and c.rn = 1
group by e.[EId]

See the demo.
Results:

> EId | HomeNum   | MobileNum | WorkNum   | WorkNumExt | MainPhType
> --: | :-------- | :-------- | :-------- | :--------- | :---------
>   1 | 111111111 | 222222222 | 333333333 | null       | Mobile    
>   2 | null      | 454545454 | 444444444 | 567        | Work      
>   3 | 777777777 | 888888888 | null      | null       | Mobile    
>   4 | null      | null      | 101010101 | 111        | Work      
>   5 | null      | null      | 456456456 | null       | Work      
>   6 | null      | null      | null      | null       | null