On LiveSQL there is a tutorial, 'Converting Rows to Columns and Back Again: Databases for Developers'
https://livesql.oracle.com/apex/livesql/file/tutorial_GNZ3LQPJ0K6RTD1NEEPNRQT0R.html It guides me through on how to pivot rows into columns and would like to know if we can calculate the newly pivoted column.
with rws as (
select location, to_char ( match_date, 'MON' ) match_month
from match_results
)
select * from rws
pivot (
count (*) for match_month in (
'JAN', 'FEB', 'MAR'
)
)How would we add to the same table a new column to subtract March and Feb?
Thank you,
With a pivot you can also alias the columns, which makes it easier to use in subsequent expressions, eg
SQL> create table match_results (
2 match_date date,
3 location varchar2(20),
4 home_team_name varchar2(20),
5 away_team_name varchar2(20),
6 home_team_points integer,
7 away_team_points integer
8 );
Table created.
SQL>
SQL> insert into match_results values ( date'2018-01-01', 'Snowley', 'Underrated United', 'Terrible Town', 2, 0 );
1 row created.
SQL> insert into match_results values ( date'2018-01-01', 'Coldgate', 'Average Athletic', 'Champions City', 1, 4 );
1 row created.
SQL> insert into match_results values ( date'2018-02-01', 'Dorwall', 'Terrible Town', 'Average Athletic', 0, 1 );
1 row created.
SQL> insert into match_results values ( date'2018-03-01', 'Coldgate', 'Average Athletic', 'Underrated United', 3, 3 );
1 row created.
SQL> insert into match_results values ( date'2018-03-02', 'Newdell', 'Champions City', 'Terrible Town', 8, 0 );
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> with rws as (
2 select location, to_char ( match_date, 'MON' ) match_month
3 from match_results
4 )
5 select location, jan, feb, mar, feb - mar as demo_expression
6 from rws
7 pivot (
8 count (*) for match_month in (
9 'JAN' as jan, 'FEB' as feb, 'MAR' as mar
10 )
11 );
LOCATION JAN FEB MAR DEMO_EXPRESSION
-------------------- ---------- ---------- ---------- ---------------
Dorwall 0 1 0 1
Coldgate 1 0 1 -1
Snowley 1 0 0 0
Newdell 0 0 1 -1