2022-05-27

Oracle - drill down the records

I have a table with services and each combination of the services have a specific cost amount. I want to filter one service and get to know, what are the services one level to the left and then choose another service from that subpart, etc.

Please see an example picture: On the left is the process of the "drill down" and on the right is the desired output. Please ignore the sum of the amounts (they are not correct).

example picture

CREATE TABLE test_table (
id              INTEGER,
costcenter      VARCHAR2(20),
service_level1  VARCHAR2(40),
service_level2  VARCHAR2(40),
service_level3  VARCHAR2(40),
service_level4  VARCHAR2(40),
amount          INTEGER);

INSERT INTO test_table (id,costcenter, service_level1, service_level2, service_level3, service_level4, amount)
VALUES ( 1, '10016831', 'U00 COGNOS AL', NULL, NUll, NULL, 50000); 
INSERT INTO test_table (id,costcenter, service_level1, service_level2, service_level3, service_level4, amount)
VALUES ( 2, '10016832', 'EXADATA Basis', 'U00 COGNOS AL', NUll, NULL, 20000); 
INSERT INTO test_table (id,costcenter, service_level1, service_level2, service_level3, service_level4, amount)
VALUES ( 3, '10016833', 'SPLUNK','EXADATA Basis', 'U00 COGNOS AL', NULL, 15000); 
INSERT INTO test_table (id,costcenter, service_level1, service_level2, service_level3, service_level4, amount)
VALUES ( 4, '10016833', 'Linux Basis', 'SPLUNK', 'EXADATA Basis', 'U00 COGNOS AL', 30000); 
INSERT INTO test_table (id,costcenter, service_level1, service_level2, service_level3, service_level4, amount)
VALUES ( 5, '10016833', 'Linux Basis', 'Oracle Admin', 'EXADATA Basis', 'U00 COGNOS AL', 20000); 
COMMIT;


No comments:

Post a Comment