How to form a pattern of data into 1 row

Question

I have a requirement of writing table records on to a flat file. Here is the requirement: I have to find all the columns for which the filtering columns are same, in such case the rows are to be written on the same line on the flat file with column name as prefix to the values. Here col1 and col2 are to form a unique record.

Input table:

col1 col2 col3 col4  
A    B     1    HELLO  
C    X     5    DEMO  
A    B     2    TEXT  

Output :

A B col31,col4HELLO col32,col4TEXT  
C X col35,col4DEMO

Answer 1

Here's a solution which works with the posted sample data and output. It works in two steps: concatenate the columns for each row then use LISTAGG() to aggregate all the rows for combinations of col1, col2.

SQL> with cte as (
  2     select col1
  3            , col2
  4            , 'col3:' || col3 ||' col4:' || col4 as col3col4
  5            , row_number() over (partition by col1, col2 order by col1, col2) as rn
  6     from input_table
  7  )
  8  select col1
  9         , col2
 10         , listagg(col3col4, ' ') within group (order by rn) as concat_output
 11  from cte
 12  group by col1, col2;

COL1 COL2 CONCAT_OUTPUT
---- ---- ----------------------------------------
A    B    col3:2 col4:TEXT col3:1 col4:HELLO
C    X    col3:5 col4:DEMO
SQL>