BTEQ 15.10.01.01 Mon Feb 05 16:11:40 2018 PID: 20704
 
+---------+---------+---------+---------+---------+---------+---------+----
/****** 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,

 *** Logon successfully completed.
 *** Teradata Database Release is 16.60d.00.05                  
 *** Teradata Database Version is 16.60d.00.05p1                  
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.
 
 *** Total elapsed time was 2 seconds.
 
+---------+---------+---------+---------+---------+---------+---------+----

.errorout stdout
 *** Error messages now directed to STDOUT.
+---------+---------+---------+---------+---------+---------+---------+----
.sidetitles on
+---------+---------+---------+---------+---------+---------+---------+----
.foldline on
+---------+---------+---------+---------+---------+---------+---------+----

delete user pivot_db;

 *** Database's tables deleted. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
drop user pivot_db;

 *** Database/User has been dropped. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
create user pivot_db as perm=10e6, password=pivot_db;

 *** User has been created. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

database pivot_db;

 *** New default database accepted. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

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

 *** Table has been created. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

insert into DailyIncome values ('SPIKE', 'FRI', 100);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'MON', 300);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'SUN', 400);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'WED', 500);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'TUE', 200);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('JOHNS', 'WED', 900);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'FRI', 100);
 *** Failure 2802 Duplicate row error in pivot_db.DailyIncome.
                Statement# 1, Info =0 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('JOHNS', 'MON', 300);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'SUN', 400);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('JOHNS', 'FRI', 300);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'TUE', 500);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'TUE', 200);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'MON', 900);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'FRI', 900);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'MON', 500);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('JOHNS', 'SUN', 600);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'FRI', 300);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'WED', 500);
 *** Failure 2802 Duplicate row error in pivot_db.DailyIncome.
                Statement# 1, Info =0 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'FRI', 300);
 *** Failure 2802 Duplicate row error in pivot_db.DailyIncome.
                Statement# 1, Info =0 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('JOHNS', 'THU', 800);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('JOHNS', 'SAT', 800);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'TUE', 100);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'THU', 300);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'WED', 500);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('SPIKE', 'SAT', 100);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'SAT', 500);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('FREDS', 'THU', 800);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
insert into DailyIncome values ('JOHNS', 'TUE', 600);

 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

/* 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;

 *** Query completed. 3 rows found. 8 columns returned. 
 *** Total elapsed time was 1 second.

VendorId  FREDS
   'MON'          500
   'TUE'          350
   'WED'          500
   'THU'          800
   'FRI'          900
   'SAT'          500
   'SUN'          400
VendorId  JOHNS
   'MON'          300
   'TUE'          600
   'WED'          900
   'THU'          800
   'FRI'          300
   'SAT'          800
   'SUN'          600
VendorId  SPIKE
   'MON'          600
   'TUE'          150
   'WED'          500
   'THU'          300
   'FRI'          200
   'SAT'          100
   'SUN'          400

+---------+---------+---------+---------+---------+---------+---------+----


/*************************************************************************************************
* CASE - 2                                                                                       *
* The 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;

 *** Query completed. One row found. 8 columns returned. 
 *** Total elapsed time was 1 second.

VendorId  SPIKE
   'MON'          900
   'TUE'          200
   'WED'          500
   'THU'          300
   'FRI'          300
   'SAT'          100
   'SUN'          400

+---------+---------+---------+---------+---------+---------+---------+----
                    

/*************************************************************************************************
* 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','SU
N'))dt   
order by VendorId;

 *** Query completed. 15 rows found. 9 columns returned. 
 *** Total elapsed time was 1 second.

    VendorId  FREDS
IncomeAmount          400
       'MON'            0
       'TUE'            0
       'WED'            0
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            1
    VendorId  FREDS
IncomeAmount          900
       'MON'            0
       'TUE'            0
       'WED'            0
       'THU'            0
       'FRI'            1
       'SAT'            0
       'SUN'            0
    VendorId  FREDS
IncomeAmount          800
       'MON'            0
       'TUE'            0
       'WED'            0
       'THU'            1
       'FRI'            0
       'SAT'            0
       'SUN'            0
    VendorId  FREDS
IncomeAmount          500
       'MON'            1
       'TUE'            1
       'WED'            1
       'THU'            0
       'FRI'            0
       'SAT'            1
       'SUN'            0
    VendorId  FREDS
IncomeAmount          200
       'MON'            0
       'TUE'            1
       'WED'            0
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            0
    VendorId  JOHNS
IncomeAmount          900
       'MON'            0
       'TUE'            0
       'WED'            1
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            0
    VendorId  JOHNS
IncomeAmount          600
       'MON'            0
       'TUE'            1
       'WED'            0
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            1
    VendorId  JOHNS
IncomeAmount          800
       'MON'            0
       'TUE'            0
       'WED'            0
       'THU'            1
       'FRI'            0
       'SAT'            1
       'SUN'            0
    VendorId  JOHNS
IncomeAmount          300
       'MON'            1
       'TUE'            0
       'WED'            0
       'THU'            0
       'FRI'            1
       'SAT'            0
       'SUN'            0
    VendorId  SPIKE
IncomeAmount          900
       'MON'            1
       'TUE'            0
       'WED'            0
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            0
    VendorId  SPIKE
IncomeAmount          500
       'MON'            0
       'TUE'            0
       'WED'            1
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            0
    VendorId  SPIKE
IncomeAmount          400
       'MON'            0
       'TUE'            0
       'WED'            0
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            1
    VendorId  SPIKE
IncomeAmount          200
       'MON'            0
       'TUE'            1
       'WED'            0
       'THU'            0
       'FRI'            0
       'SAT'            0
       'SUN'            0
    VendorId  SPIKE
IncomeAmount          300
       'MON'            1
       'TUE'            0
       'WED'            0
       'THU'            1
       'FRI'            1
       'SAT'            0
       'SUN'            0
    VendorId  SPIKE
IncomeAmount          100
       'MON'            0
       'TUE'            1
       'WED'            0
       'THU'            0
       'FRI'            1
       'SAT'            1
       'SUN'            0

+---------+---------+---------+---------+---------+---------+---------+----

/*************************************************************************************************
* 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, 'WE
D' as wed,'THU' as thu,'FRI' as fri, 'SAT' as sat, 'SUN' as sun)) dsales )

WITH DATA;

 *** Table has been created. 
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

/*************************************************************************************************
* CASE - 5                                                                                       *
* This switches the rows and columns over aka 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 p
ivot(sum(saleval) for VendorId in ('FREDS','JOHNS','SPIKE'))dt3;

 *** Query completed. 7 rows found. 4 columns returned. 
 *** Total elapsed time was 1 second.

  sales SAT
'FREDS'         500
'JOHNS'         800
'SPIKE'         100
  sales WED
'FREDS'         500
'JOHNS'         900
'SPIKE'         500
  sales SUN
'FREDS'         400
'JOHNS'         600
'SPIKE'         400
  sales FRI
'FREDS'         900
'JOHNS'         300
'SPIKE'         400
  sales THU
'FREDS'         800
'JOHNS'         800
'SPIKE'         300
  sales TUE
'FREDS'         700
'JOHNS'         600
'SPIKE'         300
  sales MON
'FREDS'         500
'JOHNS'         300
'SPIKE'        1200

+---------+---------+---------+---------+---------+---------+---------+----

.logout;
 *** Error: Unrecognized command 'LOGOUT'.
 *** BTEQ exiting due to EOF on stdin.
 *** Exiting BTEQ...
 *** RC (return code) = 8 
