DECLARE @Employee TABLE
    (
        [Employee_Id] INT IDENTITY(1, 1)
      , [Code]        NVARCHAR(10)
    ) ;

INSERT INTO @Employee
VALUES ( N'E1' ), ( N'E2' ), ( N'E3' ) ;

DECLARE @Contact TABLE
    (
        [Employee_Id]  INT
      , [PhoneType]    CHAR(1)
      , [PhoneNumber]  VARCHAR(20)
      , [IsMainNumber] BIT
    ) ;

INSERT INTO @Contact
VALUES (1, 'M', '1234567890', 1), (1, 'H', '1234567891', 0),
       (1, 'M', '1234567892', 0), (1, 'B', '1234567893', 0),
       (2, 'M', '2234567890', 0), (2, 'H', '2234567891', 1),
       (2, 'B', '2234567892', 0), (2, 'M', '2234567893', 0),
       (3, 'M', '3234567890', 0), (3, 'H', '3234567891', 0),
       (3, 'M', '3234567892', 0), (3, 'B', '3234567893', 1);

SELECT
    [E].[Employee_Id],
    [E].[Code],
    [COA].[MainPhoneNumber],
    [COA].[NonMainNumber]
FROM
    @Employee AS [E]
OUTER APPLY
    (SELECT
         MAX (IIF([C].[IsMainNumber] = 1, [C].[PhoneNumber], NULL)) [MainPhoneNumber],
         MAX (IIF([C].[IsMainNumber] = 0, [C].[PhoneNumber], NULL)) [NonMainNumber]
     FROM
         @Contact AS [C]
     WHERE
         [E].[Employee_Id] = [C].[Employee_Id]
     GROUP BY
         [C].[Employee_Id]) AS [COA] ;

Employee_Id Code    MainPhoneNumber NonMainNumber
1           E1      1234567890      1234567893
2           E2      2234567891      2234567893
3           E3      3234567893      3234567892

Employee_Id Code    MainPhoneType   MainPhoneNumber NonMainPhoneType    NonMainNumber
1           E1      M               1234567890      B                   1234567893
2           E2      H               2234567891      M                   2234567893
3           E3      B               3234567893      M                   3234567892

select e.Employee_Id, e.Code,
       c.PhoneType as MainPhoneType, c.PhoneNumber as MainPhoneNumber,
       c1.PhoneType as NonMainPhoneType, c1.PhoneNumber as NonMainNumber
from @Employee e outer apply
     (select top (1) c.PhoneType, c.PhoneNumber
      from @Contact c
      where c.Employee_Id = e.Employee_Id and
            c.IsMainNumber = 1
      order by c.phonetype
     ) c outer apply
     (select top (1) c1.PhoneType, c1.PhoneNumber
      from @Contact c1
      where c1.Employee_Id = e.Employee_Id and
            c1.IsMainNumber = 0
      order by c1.phonetype
     ) c1;

insert into #temp (Employee_Id, PhoneType, PhoneNumber, IsMainNumber)
    select Employee_Id, PhoneType, PhoneNumber, IsMainNumber
    from (select *, row_number() over (partition by Employee_Id, IsMainNumber order by PhoneType) as seq
          from @Contact
         ) c
     where seq = 1

select e.*, m.*
from @Employee e cross apply
     (select max(case when t.IsMainNumber = 1 then t.PhoneType end) as MainPhoneType,
             max(case when t.IsMainNumber = 1 then t.PhoneNumber end) as MainPhoneNumber,
             max(case when t.IsMainNumber = 0 then t.PhoneType end) as NonMainPhoneType,
             max(case when t.IsMainNumber = 0 then t.PhoneNumber end) as NonMainNumber
      from #temp t
      where t.Employee_Id = e.Employee_Id
     ) m;

select x.Employee_Id
    , x.Code
    , MainPhoneType = max(case when x.RowNum = 1 then x.PhoneType end)
    , MainPhoneNumber = max(case when x.RowNum = 1 then x.PhoneNumber end)
    , NonMainPhoneType = max(case when x.RowNum = 2 then x.PhoneType end)
    , NonMainPhoneNumber = max(case when x.RowNum = 2 then x.PhoneNumber end)
from
(
    select e.Employee_Id
        , e.Code
        , c.PhoneType
        , c.PhoneNumber
        , RowNum = ROW_NUMBER() over(partition by e.Employee_Id order by c.IsMainNumber desc, c.PhoneType) --Not sure how you determine the non MainNumber when there are several to pick from
    from @Employee e
    join @Contact c on c.Employee_Id = e.Employee_Id
) x
group by x.Employee_Id
    , x.Code

select e.Employee_Id, e.Code
       max(case when seqnum = 1 and c.PhoneType = 'M' then c.PhoneType end) as MainPhoneType
       max(case when seqnum = 1  and c.PhoneType = 'M' then x.PhoneNumber end) as MainPhoneNumber,
       max(case when seqnum = 1 and c.PhoneType <> 'M' then c.PhoneType end) as NonMainPhoneType
       max(case when seqnum = 1  and c.PhoneType <> 'M' then c.PhoneNumber end) as NonMainPhoneNumber
from @Employee e join
     (select c.*,
             row_number() over (partition by c.Employee_Id
                                             (case when PhoneType = 'M' then  'M' end)
                                order by c.PhoneNumber desc
                               ) as seqnum
      from @Contact c
     ) c
     on c.Employee_Id = e.Employee_Id
group by e.Employee_Id, e.Code;