Oracle sql pivoting values separated by commas

Question

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.

Barbaros Özhan · Accepted Answer · 2021-04-09T09:21:32.847

2

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

edited Apr 09 '21 at 09:21

answered Apr 09 '21 at 09:13

Barbaros Özhan

59,113
10
31
55

Thanks! I hadn't thought of that way. Now I don't know how to, if, let's say, the result is R, R, R, R, Y, putting 4R, Y. But I'll ask this on a separate question. – Javi Torre Apr 09 '21 at 09:21
you're welcome @JaviTorre . You can check [this](https://stackoverflow.com/a/65055157/5841306) out for dynamic option. – Barbaros Özhan Apr 09 '21 at 09:21

score 1 · Answer 2 · answered Apr 09 '21 at 11:24

1

I just find conditional aggregation so much simpler than pivot:

SELECT id,
       LISTAGG(CASE WHEN lotyp = '02' AND proid = '01' THEN lockr END, ',') WITHIN GROUP (ORDER BY lockr) as "0201",
       LISTAGG(CASE WHEN lotyp = '02' AND proid = '02' THEN lockr END, ',') WITHIN GROUP (ORDER BY lockr) as "0201",
       LISTAGG(CASE WHEN lotyp = '06' AND proid = '01' THEN lockr END, ',') WITHIN GROUP (ORDER BY lockr) as "0601",
       LISTAGG(CASE WHEN lotyp = '02' AND proid = '01' THEN lockr END, ',') WITHIN GROUP (ORDER BY lockr) as "0601"
FROM t
GROUP BY id ;

No subqueries. Just logic that clearly does what you are trying to do.

answered Apr 09 '21 at 11:24

Gordon Linoff

1,242,037
58
646
786

Thanks! This is also a good solution, although not escalable for many combinations lotyp/proid. – Javi Torre Apr 09 '21 at 13:08
@JaviTorre . . . I'm not sure what you mean by scalable. Conditional aggregation usually runs as fast as or faster than `pivot`. Plus, this avoids the aggregation in the subquery which should make it hands-down faster. – Gordon Linoff Apr 09 '21 at 13:16

Oracle sql pivoting values separated by commas

2 Answers2