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:
| 01-JAN-18 | Snowley | Underrated United | Terrible Town | 2 | 0 |
| 01-JAN-18 | Coldgate | Average Athletic | Champions City | 1 | 4 |
| 01-FEB-18 | Dorwall | Terrible Town | Average Athletic | 0 | 1 |
| 01-MAR-18 | Coldgate | Average Athletic | Underrated United | 3 | 3 |
| 02-MAR-18 | Newdell | Champions City | Terrible Town | 8 | 0 |
The desired result is:
| MATCH_DATE | LOCATION | TEAM
| HOME_OR_AWAY
| SCORE
|
|---|
| 01-JAN-18 | Snowley | Underrated United | HOME | 2 |
| 01-JAN-18 | Snowley | Terrible Town | AWAY | 0 |
| 01-JAN-18 | Coldgate | Average Athletic | HOME | 1 |
| 01-JAN-18 | Coldgate | Champions City | AWAY | 4 |
...
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?