CODEWITHSUNDEEP
sqloracle-database
Matheus Ciaramella
Matheus Ciaramella

Reputation: 53

Use values of select into columns

I don't know if the title of the question is clear enough but I have this situation:

TABLE ID, VALUE_1, VALUE_2
       1, HORSE  , 500
       1, DOG    , 400
       1, DUCK   , 300
       2, HORSE  , 500
       2, DOG    , 400
       2, DUCK   , 300

I Want to see those values like 

ID HORSE DOG DUCK
1    500 400 300
2    500 400 300

Upvotes: 0

Views: 57

Answers (4)

user5683823
user5683823

Reputation:

In Oracle 11.1 and higher, you can use the PIVOT operator. Assuming the table name is T:

select id, horse, dog, duck
from   t
pivot  (min(value_2) 
        for value_1 in ('HORSE' as horse, 'DOG' as dog, 'DUCK' as duck))
order  by id
;

In Oracle 12.1 and higher, the same can be achieved with MATCH_RECOGNIZE (and we don't need aggregation anymore):

select id, horse, dog, duck
from   t
match_recognize(
  partition by id
  measures  horse.value_2 as horse,
            dog.value_2   as dog,
            duck.value_2  as duck
  pattern   ( (horse|dog|duck)* )
  define    horse as value_1 = 'HORSE', dog as value_1 = 'DOG', 
             duck as value_1 = 'DUCK'
);

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

Use condititonal aggregation

select id,
       max(case when value_1 = 'HORSE' then value_2 end ) as horse,
       max(case when value_1 = 'DOG' then value_2 end ) as dog,
       max(case when value_1 = 'DUCK' then value_2 end ) as duck
  from tab
 group by id;

Demo

Upvotes: 1

forpas
forpas

Reputation: 164064

With conditional aggregation:

select
  ID,
  max(case when value_1 = 'HORSE' THEN value_2 end) HORSE,
  max(case when value_1 = 'DOG' THEN value_2 end) DOG,
  max(case when value_1 = 'DUCK' THEN value_2 end) DUCK
from tablename
group by ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use conditional aggregation:

select id,
       sum(case when value_1 = 'HORSE' then value_2 end) as horse,
       sum(case when value_1 = 'DOG' then value_2 end) as dog,
       sum(case when value_1 = 'DUCK' then value_2 end) as duck
from t
group by id
order by id;

Upvotes: 1

Related Questions