Toad World® Forums

oracle data transformation

[i have a temp table as belows, it includes 2 columns, 1st is the column name, 2nd is the file_name

data_file.txt

column_name file_name
col1 file1
col2 file2
col3 file1
col4 file1
col5 file2

now, i would like to output below result, which returns all the column name by file name as belows.

file_name column_name
file1 col1,col3,col4
file2 col2,col5](https://www.bleepingcomputer.com/forums/t/661978/about-how-to-write-a-shell-script-to-read-the-text-file-and-perform-transform/)

CREATE TABLE X (
A_COLUMN_NAME VARCHAR2(32 BYTE),
A_FILE_NAME VARCHAR2(32 BYTE)
)
/

SET DEFINE OFF;
Insert into X
(A_COLUMN_NAME, A_FILE_NAME)
Values
(‘col1’, ‘file1’);
Insert into X
(A_COLUMN_NAME, A_FILE_NAME)
Values
(‘col2’, ‘file2’);
Insert into X
(A_COLUMN_NAME, A_FILE_NAME)
Values
(‘col3’, ‘file1’);
Insert into X
(A_COLUMN_NAME, A_FILE_NAME)
Values
(‘col4’, ‘file1’);
Insert into X
(A_COLUMN_NAME, A_FILE_NAME)
Values
(‘col5’, ‘file2’);
COMMIT;

col a_file_name for a10
col column_name for a30

select distinct x.a_file_name, listagg (x.a_column_name, ‘,’) within group (order by x.a_file_name) over (PARTITION BY x.a_file_name) column_name
from x
order by x.a_file_name
;

A_FILE_NAM COLUMN_NAME


file1 col1,col3,col4
file2 col5,col2

Elapsed: 00:00:00.00