기존에 비슷한 프로그램 도움을 받았지만 아래 내용은 해결을 할 능력이 안되는 것 같습니다.
감사합니다.
source table srctable
FEATURE_VAL | FEATURE_UID | CNAME | FIRSTSEQ | SECSEQ |
HL3CL1 | xAqJDKUApltyNB | Left Handle Drive | 0 | 1 |
HL3CL1 | gOoJDKE4pltyNB | US | 0 | 2 |
HL3CL1 | hYqJDKUApltyNB | Bus | 0 | 3 |
HL3CL1 | hgiJDKUApltyNB | Lengthen WheelBase | 0 | 4 |
HL3CL1 | hsiJDKUApltyNB | Low ROOF | 0 | 5 |
HL3CL1 | h4mJDKUApltyNB | 2-Seat | 0 | 6 |
HL3CL1 | hwuJDKUApltyNB | 2015 M/Y | 0 | 7 |
HL3CL2 | xAqJDKUApltyNB | Left Handle Drive | 1 | 1 |
HL3CL2 | gOoJDKE4pltyNB | US | 1 | 2 |
HL3CL2 | hYqJDKUApltyNB | Bus | 1 | 3 |
HL3CL2 | hgiJDKUApltyNB | Lengthen WheelBase | 1 | 4 |
HL3CL2 | hsiJDKUApltyNB | Low ROOF | 1 | 5 |
HL3CL2 | h4mJDKUApltyNB | 2-Seat | 1 | 6 |
HL3CL2 | h0qJDKUApltyNB | 2016 M/Y | 1 | 7 |
HL3CL3 | xAqJDKUApltyNB | Left Handle Drive | 2 | 1 |
HL3CL3 | gOoJDKE4pltyNB | US | 2 | 2 |
HL3CL3 | hYqJDKUApltyNB | Bus | 2 | 3 |
HL3CL3 | hgiJDKUApltyNB | Lengthen WheelBase | 2 | 4 |
HL3CL3 | hsiJDKUApltyNB | Low ROOF | 2 | 5 |
HL3CL3 | h8iJDKUApltyNB | 3-Seat | 2 | 6 |
HL3CL3 | hwuJDKUApltyNB | 2015 M/Y | 2 | 7 |
HL3CH2 | xAqJDKUApltyNB | Left Handle Drive | 3 | 1 |
HL3CH2 | gOoJDKE4pltyNB | US | 3 | 2 |
HL3CH2 | hYqJDKUApltyNB | Bus | 3 | 3 |
HL3CH2 | hgiJDKUApltyNB | Lengthen WheelBase | 3 | 4 |
HL3CH2 | hsiJDKUApltyNB | Low ROOF | 3 | 5 |
HL3CH2 | h8iJDKUApltyNB | 3-Seat | 3 | 6 |
HL3CH2 | h0qJDKUApltyNB | 2016 M/Y | 3 | 7 |
HL3CA3 | xAqJDKUApltyNB | Right Handle Drive | 4 | 1 |
HL3CA3 | gOoJDKE4pltyNB | US | 4 | 2 |
HL3CA3 | hYqJDKUApltyNB | Bus | 4 | 3 |
HL3CA3 | hgiJDKUApltyNB | Lengthen WheelBase | 4 | 4 |
HL3CA3 | hsiJDKUApltyNB | Low ROOF | 4 | 5 |
HL3CA3 | h8uJDKUApltyNB | 12-Seat | 4 | 6 |
HL3CA3 | hwuJDKUApltyNB | 2015 M/Y | 4 | 7 |
result table rstTable
FEATURE_UID | CNAME | LV |
xAqJDKUApltyNB | Left Handle Drive | 1 |
gOoJDKE4pltyNB | US | 2 |
hYqJDKUApltyNB | Bus | 3 |
hgiJDKUApltyNB | Lengthen WheelBase | 4 |
hsiJDKUApltyNB | Low ROOF | 5 |
h4mJDKUApltyNB | 2-Seat | 6 |
hwuJDKUApltyNB | 2015 M/Y | 7 |
HL3CL1 | HL3CL1 | 8 |
h0qJDKUApltyNB | 2016 M/Y | 7 |
HL3CL2 | HL3CL2 | 8 |
h8iJDKUApltyNB | 3-Seat | 6 |
hwuJDKUApltyNB | 2015 M/Y | 7 |
HL3CL3 | HL3CL3 | 8 |
h0qJDKUApltyNB | 2016 M/Y | 7 |
HL3CH2 | HL3CH2 | 8 |
xAqJDKUApltyNB | Right Handle Drive | 1 |
gOoJDKE4pltyNB | US | 2 |
hYqJDKUApltyNB | Bus | 3 |
hgiJDKUApltyNB | Lengthen WheelBase | 4 |
hsiJDKUApltyNB | Low ROOF | 5 |
h8uJDKUApltyNB | 12-Seat | 6 |
hwuJDKUApltyNB | 2015 M/Y | 7 |
HL3CA3 | HL3CA3 | 8 |
WITH src AS ( SELECT 'HL3CL1' feature_val, 'xAqJDKUApltyNB' feature_uid, 'Left Handle Drive' cname, 0 firstseq, 1 secseq FROM dual UNION ALL SELECT 'HL3CL1', 'gOoJDKE4pltyNB', 'US' , 0, 2 FROM dual UNION ALL SELECT 'HL3CL1', 'hYqJDKUApltyNB', 'Bus' , 0, 3 FROM dual UNION ALL SELECT 'HL3CL1', 'hgiJDKUApltyNB', 'Lengthen WheelBase', 0, 4 FROM dual UNION ALL SELECT 'HL3CL1', 'hsiJDKUApltyNB', 'Low ROOF' , 0, 5 FROM dual UNION ALL SELECT 'HL3CL1', 'h4mJDKUApltyNB', '2-Seat' , 0, 6 FROM dual UNION ALL SELECT 'HL3CL1', 'hwuJDKUApltyNB', '2015 M/Y' , 0, 7 FROM dual UNION ALL SELECT 'HL3CL2', 'xAqJDKUApltyNB', 'Left Handle Drive' , 1, 1 FROM dual UNION ALL SELECT 'HL3CL2', 'gOoJDKE4pltyNB', 'US' , 1, 2 FROM dual UNION ALL SELECT 'HL3CL2', 'hYqJDKUApltyNB', 'Bus' , 1, 3 FROM dual UNION ALL SELECT 'HL3CL2', 'hgiJDKUApltyNB', 'Lengthen WheelBase', 1, 4 FROM dual UNION ALL SELECT 'HL3CL2', 'hsiJDKUApltyNB', 'Low ROOF' , 1, 5 FROM dual UNION ALL SELECT 'HL3CL2', 'h4mJDKUApltyNB', '2-Seat' , 1, 6 FROM dual UNION ALL SELECT 'HL3CL2', 'h0qJDKUApltyNB', '2016 M/Y' , 1, 7 FROM dual UNION ALL SELECT 'HL3CL3', 'xAqJDKUApltyNB', 'Left Handle Drive' , 2, 1 FROM dual UNION ALL SELECT 'HL3CL3', 'gOoJDKE4pltyNB', 'US' , 2, 2 FROM dual UNION ALL SELECT 'HL3CL3', 'hYqJDKUApltyNB', 'Bus' , 2, 3 FROM dual UNION ALL SELECT 'HL3CL3', 'hgiJDKUApltyNB', 'Lengthen WheelBase', 2, 4 FROM dual UNION ALL SELECT 'HL3CL3', 'hsiJDKUApltyNB', 'Low ROOF' , 2, 5 FROM dual UNION ALL SELECT 'HL3CL3', 'h8iJDKUApltyNB', '3-Seat' , 2, 6 FROM dual UNION ALL SELECT 'HL3CL3', 'hwuJDKUApltyNB', '2015 M/Y' , 2, 7 FROM dual UNION ALL SELECT 'HL3CH2', 'xAqJDKUApltyNB', 'Left Handle Drive' , 3, 1 FROM dual UNION ALL SELECT 'HL3CH2', 'gOoJDKE4pltyNB', 'US' , 3, 2 FROM dual UNION ALL SELECT 'HL3CH2', 'hYqJDKUApltyNB', 'Bus' , 3, 3 FROM dual UNION ALL SELECT 'HL3CH2', 'hgiJDKUApltyNB', 'Lengthen WheelBase', 3, 4 FROM dual UNION ALL SELECT 'HL3CH2', 'hsiJDKUApltyNB', 'Low ROOF' , 3, 5 FROM dual UNION ALL SELECT 'HL3CH2', 'h8iJDKUApltyNB', '3-Seat' , 3, 6 FROM dual UNION ALL SELECT 'HL3CH2', 'h0qJDKUApltyNB', '2016 M/Y' , 3, 7 FROM dual UNION ALL SELECT 'HL3CA3', 'xAqJDKUApltyNB', 'Right Handle Drive', 4, 1 FROM dual UNION ALL SELECT 'HL3CA3', 'gOoJDKE4pltyNB', 'US' , 4, 2 FROM dual UNION ALL SELECT 'HL3CA3', 'hYqJDKUApltyNB', 'Bus' , 4, 3 FROM dual UNION ALL SELECT 'HL3CA3', 'hgiJDKUApltyNB', 'Lengthen WheelBase', 4, 4 FROM dual UNION ALL SELECT 'HL3CA3', 'hsiJDKUApltyNB', 'Low ROOF' , 4, 5 FROM dual UNION ALL SELECT 'HL3CA3', 'h8uJDKUApltyNB', '12-Seat' , 4, 6 FROM dual UNION ALL SELECT 'HL3CA3', 'hwuJDKUApltyNB', '2015 M/Y' , 4, 7 FROM dual ) SELECT NVL(feature_uid, feature_val) feature_uid , NVL(cname , feature_val) cname , NVL(secseq, MAX(secseq) + 1) lv FROM (SELECT feature_val, feature_uid, cname, firstseq, secseq , ROW_NUMBER() OVER(PARTITION BY SYS_CONNECT_BY_PATH(cname, ',') ORDER BY firstseq) rn FROM src START WITH secseq = 1 CONNECT BY PRIOR firstseq = firstseq AND PRIOR secseq+1 = secseq ) WHERE rn = 1 GROUP BY firstseq, feature_val, ROLLUP((secseq, feature_uid, cname)) ORDER BY firstseq, secseq ;