Oracle Pivot Column in group query

Question

I have the following working query:

WITH pivot_data AS (
             select PSGROUP,
    PSCOLUMN as PSCOLUMN
FROM LOG_PS_STATUS
            )
    SELECT *
    FROM   pivot_data
    PIVOT (
           MAX(NULL)        --<-- pivot_clause
          FOR PSCOLUMN--<-- pivot_for_clause        
        IN  (&PS_COLUMNS.)   --<-- pivot_in_clause         

);

It shows results as expected:

Values:

PSGroup PSColumn
A           1
A           2
A           3
B           1
B           2
B           3
C           3

Result is giving like:

PSGroup(Column vertically) PSColoumn(Horizontally)

           1 2 3
A
B
C

Now I want to make PSGroup column as group of PSColumn and output should be like:

A
 1 2 3
B 
 1 2 3
C
 3

Answer 1

You can use listagg:

WITH pivot_data(PSGroup,PSColumn) AS (
    select 'A',           1 FROM dual UNION all
    select 'A',           2 FROM dual UNION all
    select 'A',           3 FROM dual UNION all
    select 'B',           1 FROM dual UNION all
    select 'B',           2 FROM dual UNION all
    select 'B',           3 FROM dual UNION all
    select 'C',           3 FROM DUAL),

    res(PSGroup,PSColumns) as(
     SELECT PSGroup, LISTAGG(PSColumn, ' ') WITHIN GROUP (order by PSColumn)
        FROM   pivot_data
        GROUP BY PSGroup)

    select DECODE(PSColumns,NULL,PSGroup,NULL) AS PSGroup, PSColumns from(
    select PSGroup, NULL as PSColumns from res
      union all
    select PSGroup, PSColumns from res)t
      ORDER BY t.PSGroup, t.PSColumns NULLS first

Also note that LISTAGG(PSColumn, ' ') WITHIN GROUP (order by PSColumn) is limited to 4000 characters