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).
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;
Comments
Post a Comment