테이블 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개의 테이블 구조로 이것이 가능할까요?
만약 테이블 구조가 잘못 되었다면, 어떻게 표현해야 저런 형태의 구조를 만들수 있는지 궁금합니다.
-- 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)) ;
-- 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 ;
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 |