데이터베이스 질문과 답변
source table:
First second Third Fouth Fifth Sixth Seventh level
01.Vehicle AA Brand AA-Brand AA01 AA01-Corona Corona 1
01.Vehicle BX Body Color BX-Body Color BX101 BX101-Yellow Yellow 2
01.Vehicle BX Body Color BX-Body Color BX103 BX103-Black Black 3
01.Vehicle BX Body Color BX-Body Color BX105 BX105-Gray Gray 4
01.Vehicle BX Body Color BX-Body Color BX150 BX150-White White 5
02.Engine AO Engine Brand AO-Engine Brand AO02 AO02-threeStar threeStar 6
02.Engine AO Engine Brand AO-Engine Brand AO01 AO01-DongF DongF 7
02.Engine AP Engine Model AP-Engine Model AP01 AP01-4A95TD 4A95TD 8
02.Engine AP Engine Model AP-Engine Model AP08 AP08-DFMB18TQA DFMB18TQA 9
02.Engine AQ Excel AQ-Excel AQ03 AQ03-6b 6b 10
02.Engine AR Job Way AR-Job Way AR02 AR02-APT APT 11
02.Engine AS Oil Injection AS-Oil Injection AS01 AS01-Multi Multi 12
target table:
contents level seq
01.Vehicle 1 1
AA 2 2
Brand 3 3
AA-Brand 4 4
AA01 5 5
AA01-Corona 6 6
Corona 7 7
BX 2 8
Body Color 3 9
BX-Body Color 4 10
BX101 5 11
BX101-Yellow 6 12
Yellow 7 13
BX103 5 14
BX103-Black 6 15
Black 7 16
BX105 5 17
BX105-Gray 6 18
Gray 7 19
BX150 5 20
BX150-White 6 21
White 7 22
...
...
아래 추가 부분은 생략
감사합니다.
죄송합니다. 엑셀 카피가 안되어서 글자가 정렬이 안되었네요.
WITH t AS
(
SELECT '01.Vehicle' c1, 'AA' c2, 'Brand' c3, 'AA-Brand' c4, 'AA01' c5, 'AA01-Corona' c6, 'Corona' c7, 1 lvl FROM dual
UNION ALL SELECT '01.Vehicle', 'BX', 'Body Color' , 'BX-Body Color' , 'BX101', 'BX101-Yellow' , 'Yellow' , 2 FROM dual
UNION ALL SELECT '01.Vehicle', 'BX', 'Body Color' , 'BX-Body Color' , 'BX103', 'BX103-Black' , 'Black' , 3 FROM dual
UNION ALL SELECT '01.Vehicle', 'BX', 'Body Color' , 'BX-Body Color' , 'BX105', 'BX105-Gray' , 'Gray' , 4 FROM dual
UNION ALL SELECT '01.Vehicle', 'BX', 'Body Color' , 'BX-Body Color' , 'BX150', 'BX150-White' , 'White' , 5 FROM dual
UNION ALL SELECT '02.Engine' , 'AO', 'Engine Brand' , 'AO-Engine Brand' , 'AO02' , 'AO02-threeStar', 'threeStar', 6 FROM dual
UNION ALL SELECT '02.Engine' , 'AO', 'Engine Brand' , 'AO-Engine Brand' , 'AO01' , 'AO01-DongF' , 'DongF' , 7 FROM dual
UNION ALL SELECT '02.Engine' , 'AP', 'Engine Model' , 'AP-Engine Model' , 'AP01' , 'AP01-4A95TD' , '4A95TD' , 8 FROM dual
UNION ALL SELECT '02.Engine' , 'AP', 'Engine Model' , 'AP-Engine Model' , 'AP08' , 'AP08-DFMB18TQA', 'DFMB18TQA', 9 FROM dual
UNION ALL SELECT '02.Engine' , 'AQ', 'Excel' , 'AQ-Excel' , 'AQ03' , 'AQ03-6b' , '6b' , 10 FROM dual
UNION ALL SELECT '02.Engine' , 'AR', 'Job Way' , 'AR-Job Way' , 'AR02' , 'AR02-APT' , 'APT' , 11 FROM dual
UNION ALL SELECT '02.Engine' , 'AS', 'Oil Injection', 'AS-Oil Injection', 'AS01' , 'AS01-Multi' , 'Multi' , 12 FROM dual
)
SELECT contents
, lv
, ROW_NUMBER() OVER(ORDER BY MIN(lvl), lv) seq
FROM t
UNPIVOT (contents FOR lv IN ( c1 AS 1
, c2 AS 2
, c3 AS 3
, c4 AS 4
, c5 AS 5
, c6 AS 6
, c7 AS 7
))
GROUP BY contents, lv
;
마농님 매번 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.