/****** In this case we have lots of vendors who report in their daily income to us, for this we have a simple table that looks like this. ******/

/* Replace the IP address with the IP address of the Vantage database for your site. */
.logon 192.0.2.10/dbc,dbc

.errorout stdout
.sidetitles on
.foldline on

delete user pivot_db;
drop user pivot_db;
create user pivot_db as perm=10e6, password=pivot_db;

database pivot_db;

create table DailyIncome(VendorId varchar(10), IncomeDay varchar(10), IncomeAmount int);

insert into DailyIncome values ('SPIKE', 'FRI', 100);
insert into DailyIncome values ('SPIKE', 'MON', 300);
insert into DailyIncome values ('FREDS', 'SUN', 400);
insert into DailyIncome values ('SPIKE', 'WED', 500);
insert into DailyIncome values ('SPIKE', 'TUE', 200);
insert into DailyIncome values ('JOHNS', 'WED', 900);
insert into DailyIncome values ('SPIKE', 'FRI', 100);
insert into DailyIncome values ('JOHNS', 'MON', 300);
insert into DailyIncome values ('SPIKE', 'SUN', 400);
insert into DailyIncome values ('JOHNS', 'FRI', 300);
insert into DailyIncome values ('FREDS', 'TUE', 500);
insert into DailyIncome values ('FREDS', 'TUE', 200);
insert into DailyIncome values ('SPIKE', 'MON', 900);
insert into DailyIncome values ('FREDS', 'FRI', 900);
insert into DailyIncome values ('FREDS', 'MON', 500);
insert into DailyIncome values ('JOHNS', 'SUN', 600);
insert into DailyIncome values ('SPIKE', 'FRI', 300);
insert into DailyIncome values ('SPIKE', 'WED', 500);
insert into DailyIncome values ('SPIKE', 'FRI', 300);
insert into DailyIncome values ('JOHNS', 'THU', 800);
insert into DailyIncome values ('JOHNS', 'SAT', 800);
insert into DailyIncome values ('SPIKE', 'TUE', 100);
insert into DailyIncome values ('SPIKE', 'THU', 300);
insert into DailyIncome values ('FREDS', 'WED', 500);
insert into DailyIncome values ('SPIKE', 'SAT', 100);
insert into DailyIncome values ('FREDS', 'SAT', 500);
insert into DailyIncome values ('FREDS', 'THU', 800);
insert into DailyIncome values ('JOHNS', 'TUE', 600);

/* A lot of data that it is hard to make something useful of, for example, say that we would like to know what the average income is for each vendor is
Or what the maximum income is for each day for a particular vendor? Enter the pivot table. */

/*************************************************************************************************
* CASE - 1                                                                                       *
* To find the average for each vendor, run this query:                                           *  
**************************************************************************************************/

select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ('MON','TUE','WED','THU','FRI','SAT','SUN')) dsales order by VendorId;


/*************************************************************************************************
* CASE - 2                                                                                       *
* To find the max income for each day for vendor SPIKE, run this query:                          *  
**************************************************************************************************/

select * from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in ('MON','TUE','WED','THU','FRI','SAT','SUN')) dsales 
where VendorId in ('SPIKE') order by VendorId;
                    

/*************************************************************************************************
* CASE - 3                                                                                       *
* This gives the IncomeDay total per VendorId per IncomeDay.                                     *  
**************************************************************************************************/

select * from DailyIncome   
pivot ( count(*) for IncomeDay in ('MON','TUE','WED','THU','FRI','SAT','SUN'))dt   
order by VendorId; 

/*************************************************************************************************
* CASE - 4                                                                                       *
* This creates a new table which contains the PIVOT result data.                                 *  
**************************************************************************************************/

create table DailyIncome_sales as 
(
select * from DailyIncome
pivot (sum (IncomeAmount) for IncomeDay in ('MON' as mon,'TUE' as tue, 'WED' as wed,'THU' as thu,'FRI' as fri, 'SAT' as sat, 'SUN' as sun)) dsales )
WITH DATA;

/*************************************************************************************************
* CASE - 5                                                                                       *
* This switches the rows and columns over (a transpose). It does so by chaining a unpivot        *
* followed by an pivot.                                                                          *  
**************************************************************************************************/

sel * from (sel * from DailyIncome_sales
unpivot(saleval for sales in (MON, TUE, WED, THU, FRI, SAT, SUN))dt1)dt2 pivot(sum(saleval) for VendorId in ('FREDS','JOHNS','SPIKE'))dt3;

.logout;