-- MSSQL --
WITH tab_b AS
(
SELECT 1 Seq, '2018' yyyy, '18A765' OrderNo, 'A' CustNm
UNION ALL SELECT  2, '2019', '20B001', 'A'
UNION ALL SELECT  3, '2019', '20B002', 'A'
UNION ALL SELECT  4, '2019', '20B003', 'B'
UNION ALL SELECT  5, '2019', '20B004', 'B'
UNION ALL SELECT  6, '2019', '20A141', 'B'
UNION ALL SELECT  7, '2019', '20A142', 'B'
UNION ALL SELECT  8, '2019', '20B010', 'B'
UNION ALL SELECT  9, '2019', '20B011', 'D'
UNION ALL SELECT 10, '2021', '21A001', 'D'
UNION ALL SELECT 11, '2021', '21A002', 'D'
UNION ALL SELECT 12, '2021', '21A003', 'D'
)
, tab_c AS
(
SELECT 2 Seq, '광학팀' Team, 'Delivery' Divi, 'Laser' Detail, '20B001' OrderNo, null Result
UNION ALL SELECT  9, '광학팀', 'Delivery', 'Chiller'      , '20B001', null
UNION ALL SELECT 10, '광학팀', 'Delivery', 'BET'          , '20B001', 'O'
UNION ALL SELECT 11, '광학팀', 'Delivery', 'BET Mount'    , '20B001', 'O'
UNION ALL SELECT 12, '광학팀', 'Delivery', 'ATT'          , '20B001', 'X'
UNION ALL SELECT 13, '광학팀', 'Delivery', 'Wave Plate'   , '20B001', null
UNION ALL SELECT 14, '광학팀', 'Delivery', 'POL'          , '20B001', null
UNION ALL SELECT 15, '광학팀', 'Delivery', 'Mirror 종류'  , '20B001', null
UNION ALL SELECT 16, '광학팀', 'Delivery', 'Mirror Spring', '20B001', null
UNION ALL SELECT 17, '광학팀', 'Head'    , 'Shutter 위치' , '20B001', null
UNION ALL SELECT 18, '광학팀', 'Head'    , 'Power Meter'  , '20B001', null
UNION ALL SELECT 19, '광학팀', 'Delivery', 'BET'          , '20B00４', 'O'
)
SELECT CASE Detail WHEN 'OrderNo' THEN 0 ELSE 1 END seq
     , a.*
  FROM (SELECT Seq
             , CAST(OrderNo AS VARCHAR(6)) OrderNo
             , CAST(CustNm  AS VARCHAR(6)) CustNm
             , '' Team
             , '' Divi
          FROM tab_b
        ) a
 UNPIVOT (v FOR Detail IN (OrderNo, CustNm)) a
 PIVOT (MIN(v) FOR Seq IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) a
 UNION
SELECT *
  FROM (SELECT c.Seq
             , c.Team
             , c.Divi
             , c.Detail
             , c.Result
             , b.Seq Seq_b
          FROM tab_b b
         INNER JOIN tab_c c
            ON b.OrderNo = c.OrderNo
        ) a
 PIVOT (MIN(Result) FOR Seq_b IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) a
;

대략적으로 원하는 형태로 출력되는데 한가지 오류가 있습니다.
tab_c 테이블의 결과가 아래와 같이 두줄로 표시됩니다.
                                                       2            5
광학팀, Delivery, BET          , 20B001,       O
광학팀, Delivery, BET          , 20B004,                     O

원하는 결과는 아래와 같이 team, divi, detail이 같으면 같은 라인에 표시되길 원합니다.
                                                       2            5
광학팀, Delivery, BET          , 20B001,       O            O

도움 요청드립니다.