select 'SCOTT' login_id
,'X' alpha, 'Y' beta
,10 catgy1
,20 catgy2
,30 catgy3
,40 catgy4
,50 catgy5
,60 catgy6
from dual | LOGIN_ID | ALPHA | BETA | CATGY1 | CATGY2 | CATGY3 | CATGY4 | CATGY5 | CATGY6 | SCOTT | X | Y | 10 | 20 | 30 | 40 | 50 | 60 |
|---|
select login_id, alpha, beta, catgy, quota
from (
select 'SCOTT' login_id
,'X' alpha, 'Y' beta
,10 catgy1
,20 catgy2
,30 catgy3
,40 catgy4
,50 catgy5
,60 catgy6
from dual
)
unpivot
(
quota
for catgy in
(
catgy1 as 'CATGY1'
,catgy2 as 'CATGY2'
,catgy3 as 'CATGY3'
,catgy4 as 'CATGY4'
,catgy5 as 'CATGY5'
,catgy6 as 'CATGY6'
)
)| LOGIN_ID | ALPHA | BETA | CATGY | QUOTA | SCOTT | X | Y | CATGY1 | 10 | SCOTT | X | Y | CATGY2 | 20 | SCOTT | X | Y | CATGY3 | 30 | SCOTT | X | Y | CATGY4 | 40 | SCOTT | X | Y | CATGY5 | 50 | SCOTT | X | Y | CATGY6 | 60 |
|---|
create table aus_attractions(id number, city varchar2(50)
, attraction1 varchar2(50)
, attraction2 varchar2(50)
, reason1 varchar2(50)
, reason2 varchar2(50)
)Table created.
insert into aus_attractions values (1, 'Perth','weather','beaches','sunny','white sand')1 row(s) inserted.
insert into aus_attractions values (2, 'Sydney','bridge','blue mountains','climb','scenic')1 row(s) inserted.
insert into aus_attractions values (3, 'Melbourne','culture','aussie rules','activities','crowds')1 row(s) inserted.
select id, city
,attraction1,attraction2
,reason1, reason2
from aus_attractions| ID | CITY | ATTRACTION1 | ATTRACTION2 | REASON1 | REASON2 | 1 | Perth | weather | beaches | sunny | white sand | 2 | Sydney | bridge | blue mountains | climb | scenic | 3 | Melbourne | culture | aussie rules | activities | crowds |
|---|
select id, city, attraction, reason, rec_nbr
from ( -- original query:
(select id, city
,attraction1,attraction2
,reason1, reason2
from aus_attractions
)
unpivot -- the magic operator
((attraction, reason) -- names of replacement columns
for rec_nbr in ( -- new column defining data source in literal alias below
-- split each group of fields in here
(attraction1, reason1) as 'REC1'
,(attraction2, reason2) as 'REC2'
)
)
)| ID | CITY | ATTRACTION | REASON | REC_NBR | 1 | Perth | weather | sunny | REC1 | 1 | Perth | beaches | white sand | REC2 | 2 | Sydney | bridge | climb | REC1 | 2 | Sydney | blue mountains | scenic | REC2 | 3 | Melbourne | culture | activities | REC1 | 3 | Melbourne | aussie rules | crowds | REC2 |
|---|