CODEWITHSUNDEEP
sqloracle-databaseoracle11gunpivot
Mateusz Osiński
Mateusz Osiński

Reputation: 11

oracle SQL - unpivot

Can u tell me, why it wont work?:)

    select *
from( select r.region_id, c.country_id
from countries c join regions r on r.region_id = c.region_id)
unpivot(
    valuee for columnValue in (r.region_id))

ORA-01748: only simple column names allowed here 01748. 00000 - "only simple column names allowed here"

Upvotes: 0

Views: 288

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

Your query is strange and it isn't really clear what you're trying to achieve; but following up on @VBoka's correction of your code, you don't actually need the join at all (unless you have countries with non-existent regions) - you can do:

select *
from (
  select region_id, country_id
 from countries
)
unpivot(valuee for columnValue in (region_id));

But you can get the same result without unpivoting; with the join if you have a real reason to include it:

select c.country_id, 'REGION_ID' as columnvalue, r.region_id as valuee
from countries c
join regions r on r.region_id = c.region_id;

or without the join:

select country_id, 'REGION_ID' as columnvalue, region_id as valuee
from countries;

Either way you get a result set with one row for every country.

Upvotes: 0

VBoka
VBoka

Reputation: 9083

With this part:

select *

You are selecting columns: region_id and country_id from your inner select. So you do not need r.region_id in your UNPIVOT section, only region_id. This code is correct(without error):

select *
from(select r.region_id
            , c.country_id
     from countries c 
     join regions r on r.region_id = c.region_id)
unpivot(valuee for columnValue in (region_id));

Upvotes: 1

Related Questions