Oracle sql pivoting values separated by commas

I have the below input

ID	LOTYP	PROID	LOCKR
XXXXX	06	01	Y
XXXXX	06	02	X
XXXXX	06	02	S
XXXXX	06	01	R
XXXXX	02	01	Y
XXXXX	02	02	X
XXXXX	02	02	S
XXXXX	02	01	R
YYYYY	06	01	Y
YYYYY	06	02	X
YYYYY	06	02	S
YYYYY	06	01	R
YYYYY	02	01	Y
YYYYY	02	02	X
YYYYY	02	02	S
YYYYY	02	01	R

I would like the below output

ID	0201	0202	0601	0602
XXXXX	R, Y	S, X	R, Y	S, X
YYYYY	R, Y	S, X	R, Y	S, X

I can pivot and listagg separately but I’m struggling to combine them to produce the desired output.

Answer

You can pivot the grouped results for id and concatenated (lotyp||proid) columns such as

SELECT *
  FROM
  (
    SELECT id,lotyp||proid As title,
           LISTAGG(lockr,',') WITHIN GROUP (ORDER BY lotyp||proid) AS value
      FROM t
     GROUP BY id,lotyp||proid )
 PIVOT (
         MAX(value) FOR title IN ('0201' AS "0201",
                                  '0202' AS "0202",
                                  '0601' AS "0601",
                                  '0602' AS "0602"))   
 ORDER BY id;

ID	0201	0202	0601	0602
XXXXX	R,Y	S,X	R,Y	S,X
YYYYY	R,Y	S,X	R,Y	S,X

Demo