Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Matrix query

Arif2018Aug 12 2021

i want to list our all possible values in matrix format. i have a table with following values with one specific column having distinct values (company ,org_code,site) based on which i want to group the rows values (code_part,code_part_val) as columns, i am not sure if it is possible.

[code]
CREATE TABLE POST_DATA(COMP VARCHAR2(12),CODE_PART VARCHAR2(5),ORG_CODE VARCHAR2(12), SITE VARCHAR2(5), CODE_PART_VAL VARCHAR2(20))

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','CS','CE01','CCS1-C02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','ES','CE01','CES1-C02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','FS','CE01','CFS1-C02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C03','B','FS','CME01','CMES1-C03');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','CS','CE01','CHM02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','ES','CE01','SER04');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','FS','CE01','SER01');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C03','C','FS','CME01','CMES01');

--needed output based on distinct column code_part

C02, B,C,'CS',CCS1-C02,CHM02
C02, B,C,'ES',CES1-C02,SER04
C02, B,C,'ES',CFS1-C02,SER01
C03, B,C,'FS',CMES1-C03,CMES01

[/CODE]

This post has been answered by Jan Gorkow on Sep 4 2021
Jump to Answer

Comments

Processing

Post Details

Added on Aug 12 2021
5 comments
390 views