DB version: 19.12
I have a table like below
create table idx_meta_data (index_name varchar2(30), column_name varchar2(30), column_postion number);
insert into idx_meta_data values ('EMP_IDX1', 'EMP_ID', 1);
insert into idx_meta_data values ('EMP_IDX2', 'EMP_ID', 1);
insert into idx_meta_data values ('EMP_IDX2', 'EMP_FIRST_NAME', 2);
insert into idx_meta_data values ('EMP_IDX2', 'EMP_LAST_NAME', 3);
insert into idx_meta_data values ('EMP_IDX3', 'EMP_ID', 1);
insert into idx_meta_data values ('EMP_IDX3', 'DEPT_ID', 2);
insert into idx_meta_data values ('EMP_IDX3', 'PAYROLL_CODE', 3);
insert into idx_meta_data values ('EMP_IDX3', 'LOCATION_CODE', 4);
commit;
break on index_name SKIP PAGE on INDEX_NAME SKIP 1
SQL> select * from idx_meta_data;
INDEX_NAME COLUMN_NAME COLUMN_POSTION
------------------------------ ------------------------------ --------------
EMP_IDX1 EMP_ID 1
INDEX_NAME COLUMN_NAME COLUMN_POSTION
------------------------------ ------------------------------ --------------
EMP_IDX2 EMP_ID 1
EMP_FIRST_NAME 2
EMP_LAST_NAME 3
INDEX_NAME COLUMN_NAME COLUMN_POSTION
------------------------------ ------------------------------ --------------
EMP_IDX3 EMP_ID 1
DEPT_ID 2
PAYROLL_CODE 3
LOCATION_CODE 4
8 rows selected.
Is there a way, I can print the column names (rows of COLUMN_NAME
column) as comma separated horizontally ? I think it is called 'Pivot' or something in the SQL world ?
Expected output:
So, for EMP_IDX3, the output should be like below. Row values appear in one column but horizontally as comma separated.
Not need to print COLUMN_POSTION
INDEX_NAME COLUMN_NAME
------------------------------ -----------------------------
EMP_IDX3 EMP_ID,DEPT_ID,PAYROLL_CODE,LOCATION_CODE