WITH daily_work AS
(
SELECT 'JISI2304182' jisi_no, 1 jisi_serial, 1 sort, '제품명1' p_name
UNION ALL SELECT 'JISI2304182',  2, 1, '제품명2' 
UNION ALL SELECT 'JISI2304182',  3, 1, '제품명3' 
UNION ALL SELECT 'JISI2304182',  4, 1, '제품명4' 
UNION ALL SELECT 'JISI2304182',  5, 1, '제품명5' 
UNION ALL SELECT 'JISI2304182',  6, 1, '제품명6' 
UNION ALL SELECT 'JISI2304182',  7, 1, '제품명7' 
UNION ALL SELECT 'JISI2304182',  8, 1, '제품명8' 
UNION ALL SELECT 'JISI2304183',  1, 1, '제품명1' 
UNION ALL SELECT 'JISI2304183',  2, 1, '제품명2' 
UNION ALL SELECT 'JISI2304183',  3, 1, '제품명3' 
UNION ALL SELECT 'JISI2304183',  4, 2, '제품명4' 
UNION ALL SELECT 'JISI2304183',  5, 2, '제품명5' 
UNION ALL SELECT 'JISI2304183',  6, 2, '제품명6' 
UNION ALL SELECT 'JISI2304183',  7, 2, '제품명7' 
UNION ALL SELECT 'JISI2304183',  8, 2, '제품명8' 
UNION ALL SELECT 'JISI2304183',  9, 2, '제품명9' 
UNION ALL SELECT 'JISI2304183', 10, 2, '제품명10'
UNION ALL SELECT 'JISI2304183', 11, 2, '제품명11'
UNION ALL SELECT 'JISI2304183', 12, 2, '제품명12'
UNION ALL SELECT 'JISI2304183', 13, 2, '제품명13'
UNION ALL SELECT 'JISI2304183', 14, 2, '제품명14'
UNION ALL SELECT 'JISI2304183', 15, 2, '제품명15'
)
SELECT *
  FROM (SELECT jisi_no, sort
             , CEILING(rn / 5.) sort_
             , CONCAT('P_NAME', (rn - 1) % 5 + 1) gb
             , p_name
          FROM (SELECT jisi_no, sort, jisi_serial, p_name
                     , ROW_NUMBER() OVER(PARTITION BY jisi_no, sort ORDER BY jisi_serial) rn
                  FROM daily_work
                ) a
        ) a
 PIVOT (MIN(p_name) FOR gb IN ([P_NAME1], [P_NAME2], [P_NAME3], [P_NAME4], [P_NAME5])) a
 ORDER BY jisi_no, sort, sort_
;

안녕하세요 답변주신 방법으로 코드를 작성을 했습니다!

저는 INNER JOIN을 사용해서 원하는 결과 값을 얻었는데 INNER JOIN 아닌 다른 간결한 문장을 사용할 수 있을까요?

결과 값

입력한 코드

SELECT PVT_MTB.JISI_NO, PVT_MTB.SORT, PVT_MTB.SORT_, MTB_NAME1, JISI_SERIAL1, MTB_NAME2, JISI_SERIAL2, MTB_NAME3, JISI_SERIAL3, MTB_NAME4, JISI_SERIAL4, MTB_NAME5, JISI_SERIAL5
FROM 
(
    SELECT JISI_NO, SORT, CEILING(RN / 5.) SORT_, CONCAT('MTB_NAME', (RN - 1) % 5 + 1) AS GB,
    RIGHT(MTB_NAME,CHARINDEX('|', REVERSE(MTB_NAME))-1) AS MTB_NAME 
    FROM 
    (
        SELECT JISI_NO, SORT, CONVERT(VARCHAR(10), JISI_SERIAL) + '|' + 
        (SELECT MTB_NAME FROM ORDER_BODY WHERE ORDER_NO = X.ORDER_NO AND ORDER_SERIAL = X.ORDER_SERIAL) AS MTB_NAME,
        ROW_NUMBER() OVER(PARTITION BY JISI_NO, SORT ORDER BY JISI_SERIAL) RN
        FROM ORDER_DAILY_WORK X
    ) A
) A
PIVOT (
    MIN(MTB_NAME) FOR GB IN ([MTB_NAME1], [MTB_NAME2], [MTB_NAME3], [MTB_NAME4], [MTB_NAME5])
) AS PVT_MTB
INNER JOIN 
(
    SELECT JISI_NO, SORT, CEILING(RN / 5.) SORT_, CONCAT('JISI_SERIAL', (RN - 1) % 5 + 1) AS JS,
    LEFT(MTB_NAME, CHARINDEX('|', MTB_NAME)-1) AS JISI_SERIAL 
    FROM 
    (
        SELECT JISI_NO, SORT, CONVERT(VARCHAR(10), JISI_SERIAL) + '|' + 
        (SELECT MTB_NAME FROM ORDER_BODY WHERE ORDER_NO = X.ORDER_NO AND ORDER_SERIAL = X.ORDER_SERIAL) AS MTB_NAME,
        ROW_NUMBER() OVER(PARTITION BY JISI_NO, SORT ORDER BY JISI_SERIAL) RN
        FROM ORDER_DAILY_WORK X
    ) A
) A
PIVOT (
    MIN(JISI_SERIAL) FOR JS IN ([JISI_SERIAL1], [JISI_SERIAL2], [JISI_SERIAL3], [JISI_SERIAL4], [JISI_SERIAL5])
) AS PVT_JS
ON PVT_MTB.JISI_NO = PVT_JS.JISI_NO AND PVT_MTB.SORT = PVT_JS.SORT AND PVT_MTB.SORT_ = PVT_JS.SORT_
ORDER BY PVT_MTB.JISI_NO, PVT_MTB.SORT, PVT_MTB.SORT_
 

SELECT jisi_no
     , sort
     , sort_
     , MIN(CASE gb WHEN 1 THEN mtb_name    END)    mtb_name1
     , MIN(CASE gb WHEN 1 THEN jisi_serial END) jisi_serial1
     , MIN(CASE gb WHEN 2 THEN mtb_name    END)    mtb_name2
     , MIN(CASE gb WHEN 2 THEN jisi_serial END) jisi_serial2
     , MIN(CASE gb WHEN 3 THEN mtb_name    END)    mtb_name3
     , MIN(CASE gb WHEN 3 THEN jisi_serial END) jisi_serial3
     , MIN(CASE gb WHEN 4 THEN mtb_name    END)    mtb_name4
     , MIN(CASE gb WHEN 4 THEN jisi_serial END) jisi_serial4
     , MIN(CASE gb WHEN 5 THEN mtb_name    END)    mtb_name5
     , MIN(CASE gb WHEN 5 THEN jisi_serial END) jisi_serial5
  FROM (SELECT jisi_no
             , sort
             , jisi_serial
             , mtb_name
             , CEILING(rn / 5.) sort_
             , (RN - 1) % 5 + 1 gb
          FROM (SELECT a.jisi_no
                     , a.sort
                     , a.jisi_serial
                     , b.mtb_name
                     , ROW_NUMBER() OVER(PARTITION BY a.jisi_no, a.sort ORDER BY a.jisi_serial) rn
                  FROM order_daily_work a
                 INNER JOIN order_body b
                    ON a.order_no     = b.order_no
                   AND a.order_serial = b.order_serial
                ) a
        ) a
 GROUP BY jisi_no, sort, sort_
 ORDER BY jisi_no, sort, sort_
;