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!

Print rows as columns ; basic Pivot functionality

Peasant815 days ago

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                
This post has been answered by James Su on Oct 1 2025
Jump to Answer
Comments
Post Details
Added 5 days ago
5 comments
81 views