데이터베이스 질문과 답변

컬럼 동적 쿼리 4

by 고재원 [SQL Query] [2022.10.06 08:41:26]

테이블 A

Seq Team Divi Detail
2 광학팀 Delivery Laser
9 광학팀 Delivery Chiller
10 광학팀 Delivery BET
11 광학팀 Delivery BET Mount
12 광학팀 Delivery ATT
13 광학팀 Delivery Wave Plate
14 광학팀 Delivery POL
15 광학팀 Delivery Mirror 종류
16 광학팀 Delivery Mirror Spring
17 광학팀 Head Shutter 위치
18 광학팀 Head Power Meter

 

테이블 B

Seq yyyy OrderNo CustNm
1 2018 18A765 A
2 2019 20B001 A
3 2019 20B002 A
4 2019 20B003 B
5 2019 20B004 B
6 2019 20A141 B
7 2019 20A142 B
8 2019 20B010 B
9 2019 20B011 D
10 2021 21A001 D
11 2021 21A002 D
12 2021 21A003 D

 

테이블  C

Seq Team Divi Detail OrderNo Result
2 광학팀 Delivery Laser 20B001  
9 광학팀 Delivery Chiller 20B001  
10 광학팀 Delivery BET 20B001 O
11 광학팀 Delivery BET Mount 20B001 O
12 광학팀 Delivery ATT 20B001 X
13 광학팀 Delivery Wave Plate 20B001  
14 광학팀 Delivery POL 20B001  
15 광학팀 Delivery Mirror 종류 20B001  
16 광학팀 Delivery Mirror Spring 20B001  
17 광학팀 Head Shutter 위치 20B001  
18 광학팀 Head Power Meter 20B001  

 

최종 원한는 화면

      (동적)  
번호 18A765 20B001 20B002 20B003 20B004 20A141 20A142 20B010 20B011 21A001 21A002 21A003 …n
NO A A A B B B B B D D D D  
Team Divi Detail 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery Laser 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery Chiller 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery BET 　 O 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery BET Mount 　 O  <-이렇게 값 등록 　 　 　 　 　 　 　 　 　  
광학팀 Delivery ATT 　 X 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery Wave Plate 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery POL 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery Mirror 종류 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Delivery Mirror Spring 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Head Shutter 위치 　 　 　 　 　 　 　 　 　 　 　 　  
광학팀 Head Power Meter 　 　 　 　 　 　 　 　 　 　 　 　  

 

현재 3개의 테이블 구조로 이것이 가능할까요?

만약 테이블 구조가 잘못 되었다면, 어떻게 표현해야 저런 형태의 구조를 만들수 있는지  궁금합니다.

by 마농 [2022.10.06 09:50:59] 
-- Oracle --
WITH tab_b AS
(
SELECT 1 Seq, '2018' yyyy, '18A765' OrderNo, 'A' CustNm FROM dual
UNION ALL SELECT  2, '2019', '20B001', 'A' FROM dual
UNION ALL SELECT  3, '2019', '20B002', 'A' FROM dual
UNION ALL SELECT  4, '2019', '20B003', 'B' FROM dual
UNION ALL SELECT  5, '2019', '20B004', 'B' FROM dual
UNION ALL SELECT  6, '2019', '20A141', 'B' FROM dual
UNION ALL SELECT  7, '2019', '20A142', 'B' FROM dual
UNION ALL SELECT  8, '2019', '20B010', 'B' FROM dual
UNION ALL SELECT  9, '2019', '20B011', 'D' FROM dual
UNION ALL SELECT 10, '2021', '21A001', 'D' FROM dual
UNION ALL SELECT 11, '2021', '21A002', 'D' FROM dual
UNION ALL SELECT 12, '2021', '21A003', 'D' FROM dual
)
, tab_c AS
(
SELECT 2 Seq, '광학팀' Team, 'Delivery' Divi, 'Laser' Detail, '20B001' OrderNo, null Result FROM dual
UNION ALL SELECT  9, '광학팀', 'Delivery', 'Chiller'      , '20B001', null FROM dual
UNION ALL SELECT 10, '광학팀', 'Delivery', 'BET'          , '20B001', 'O'  FROM dual
UNION ALL SELECT 11, '광학팀', 'Delivery', 'BET Mount'    , '20B001', 'O'  FROM dual
UNION ALL SELECT 12, '광학팀', 'Delivery', 'ATT'          , '20B001', 'X'  FROM dual
UNION ALL SELECT 13, '광학팀', 'Delivery', 'Wave Plate'   , '20B001', null FROM dual
UNION ALL SELECT 14, '광학팀', 'Delivery', 'POL'          , '20B001', null FROM dual
UNION ALL SELECT 15, '광학팀', 'Delivery', 'Mirror 종류'  , '20B001', null FROM dual
UNION ALL SELECT 16, '광학팀', 'Delivery', 'Mirror Spring', '20B001', null FROM dual
UNION ALL SELECT 17, '광학팀', 'Head'    , 'Shutter 위치' , '20B001', null FROM dual
UNION ALL SELECT 18, '광학팀', 'Head'    , 'Power Meter'  , '20B001', null FROM dual
)
SELECT ROWNUM - 1 seq
     , a.*
  FROM (SELECT Seq, OrderNo, CustNm, '' Team, '' Divi FROM tab_b)
 UNPIVOT (v FOR Detail IN (OrderNo, CustNm))
 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
             , tab_c c
         WHERE b.OrderNo = c.OrderNo
        )
 PIVOT (MIN(Result) FOR Seq_b IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
;

 

by 고재원 [2022.10.06 11:44:08]

MSSQL 2005 버전입니다.

실행하면 

메시지 156, 수준 15, 상태 1, 줄 33
키워드 'UNPIVOT' 근처의 구문이 잘못되었습니다.
메시지 156, 수준 15, 상태 1, 줄 47
키워드 'PIVOT' 근처의 구문이 잘못되었습니다.

이렇게 오류 발생하네요.

by 마농 [2022.10.06 12:33:37] 
-- 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
)
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
;

 

by 고재원 [2022.10.06 14:03:59]

감사합니다. 막막했었는데 ^^

by 고재원 [2022.10.07 11:31:20]


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)

이렇게 수정했는데.. 다른 수주 건으로 내용을 등록하면 아래 표와 같이 가장 하단에 출력됩니다.

빨간색 바탕에 출력되어야 하는데. 테이블 구조는 임시 테이블 구조와 동일합니다.

15번 줄 추가했을대..

seq Team Divi Detail 1 2 3 4 5 6
0     OrderNo 18A765 20B001 20B002 20B003 20B004 20A141
1     CustNm UXN   비에이 비에이 비에이 비에이 비에이
1 광학팀 Delivery Laser NULL 1 NULL NULL 1 NULL
2 광학팀 Delivery Chiller NULL 2 NULL NULL NULL NULL
3 광학팀 Delivery BET NULL 3 NULL NULL NULL NULL
4 광학팀 Delivery BET Mount NULL 4 NULL NULL NULL NULL
5 광학팀 Delivery ATT NULL 5 NULL NULL NULL NULL
6 광학팀 Delivery Wave Plate NULL 6 NULL NULL NULL NULL
7 광학팀 Delivery POL NULL 7 NULL NULL NULL NULL
8 광학팀 Delivery Mirror 종류 NULL 8 NULL NULL NULL NULL
9 광학팀 Delivery Mirror Spring NULL 9 NULL NULL NULL NULL
10 광학팀 Head Shutter 위치 NULL 10 NULL NULL NULL NULL
11 광학팀 Head Power Meter NULL 11 NULL NULL NULL NULL
12 광학팀 Head LPC NULL 12 NULL NULL NULL NULL
13 광학팀 Head TC Lens NULL 13 NULL NULL NULL NULL
14 광학팀 Head Lens Mounting 방법 NULL 14 NULL NULL NULL NULL
15 광학팀 Delivery Laser NULL NULL NULL NULL 1 NULL

 

 

by 마농 [2022.10.07 12:50:23]

15번을 추가하는데 왜 1번에 나와야 하나요?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입