A 테이블

B 테이블

C 테이블 (idx 컬럼 추가 - A테이블 Seq 값)

여기서 어떤식으로 변경해야 하나요??

Declare @Columns Varchar(max), @SQL Varchar(max), @SQL1 Varchar(max)
Set @Columns = ''

Select @Columns = @Columns + '[' + Convert(varchar(10),Seq) + '],'
    From (
        Select Seq From SPSSCOrderNo
        ) AS ods
Set @Columns = LEFT(@Columns, LEN(@Columns) - 1)
------------

Set @SQL = ''
Set @SQL1 = ''

SET @SQL = '
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 SPSSCTitle
        ) a
 UNPIVOT (v FOR Detail IN (OrderNo, CustNm)) a
 PIVOT (MIN(v) FOR Seq IN (' + @Columns + ')) a
UNION
SELECT *
  FROM (SELECT c.Seq
             , c.Team
             , c.Divi
             , c.Detail
             , c.Result
             , b.Seq AS Seq_b
          FROM SPSSCTitle b
         INNER JOIN SPSSCDetailOrder c
            ON b.OrderNo = c.OrderNo
        ) a
 PIVOT (MIN(Result) FOR Seq_b IN (' + @Columns + ')) a
'
EXEC(@SQL)

최종 원한는 화면

-- 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

;

현재이렇게 출력됨

원하는 출력형태