CODEWITHSUNDEEP
sqloracle-databasecase
Jovani
Jovani

Reputation: 127

How do I group values ​on a single line in select oracle

I have a table in the following format:

place   stage   type
AFA11    1     TYPE_A                                                            
AFA11    2     TYPE_B                                                           
AFA12    1     TYPE_A                                                            
AFA12    2     TYPE_B

The query that i'm trying is:

SELECT place,
CASE when type = 'TYPE_A' THEN stage end type_A ,
CASE when type= 'TYPE_B' THEN stage end type_B 
FROM table  WHERE place LIKE '%AFA%'

but it's returning like that:

place   type_A  type_B
AFA11    1       null
AFA11   null      2
AFA12    1       null
AFA12   null      2

But i need the return like that:

place   type_A  type_B
AFA11     1       2
AFA12     1       2

How i could have this return?

Upvotes: 0

Views: 34

Answers (1)

jarlh
jarlh

Reputation: 44766

You can GROUP BY, and use case expressions to do conditional aggregation:

SELECT place,
       MAX(CASE when type = 'TYPE_A' THEN stage end) type_A ,
       MAX(CASE when type= 'TYPE_B' THEN stage end) type_B 
FROM table  WHERE place LIKE '%AFA%'
GROUP BY place

Upvotes: 2

Related Questions