Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UNPIVOT for two groups of columns

foxhoundFeb 7 2019 — edited Feb 7 2019

Hey, I'm doing now devgym class, 'Databases for Developers: Next Level: Pivot and Unpivot' by Chris Saxon. I wish to do UNPIVOT but for two groups of columns (HOME_TEAM_NAME, AWAY_TEAM_NAME) and (HOME_TEAM_POINTS AWAY_TEAM_POINTS) in one unpivot query but can't find any informqation if it is possible.

The table is:

MATCH_DATE

LOCATION

HOME_TEAM_NAME

AWAY_TEAM_NAME

HOME_TEAM_POINTS

AWAY_TEAM_POINTS

01-JAN-18SnowleyUnderrated UnitedTerrible Town20
01-JAN-18ColdgateAverage AthleticChampions City14
01-FEB-18DorwallTerrible TownAverage Athletic01
01-MAR-18ColdgateAverage AthleticUnderrated United33
02-MAR-18NewdellChampions CityTerrible Town80

The desired result is:

MATCH_DATELOCATIONTEAM
HOME_OR_AWAY
SCORE
01-JAN-18SnowleyUnderrated UnitedHOME2
01-JAN-18SnowleyTerrible TownAWAY0
01-JAN-18ColdgateAverage AthleticHOME1
01-JAN-18ColdgateChampions CityAWAY4

...

I can do UNPIVOT for first and single group as:

SELECT

     match_date,

     location,

     team,

     home_or_away

FROM match_result

UNPIVOT (

     team FOR home_or_away IN (

          home_team_name AS 'HOME',

          away_team_name AS 'AWAY

     )

);

But how can I (if it is possible at all) add the last column named SCORE?

Comments
Post Details
Added on Feb 7 2019
1 comment
1,517 views