Info on PIVOT
Database tables are structured in columns and rows. However, some data lends itself to switching row data as column data for expository purposes. The pivot operation in SQL allows the developer to arrange row data as column fields. For example, if there are two customers who have both visited a store exactly four times, and you want to compare the amount of money spent by each customer on each visit, you can implement the pivot operation.
Step 1: Create a table. To create the table enter the database and type the following:
create table visits( pkey int(11) not null auto_increment, name varchar(15), num_visit int, amount int,PRIMARY KEY (pkey));
This creates a table named "visits" with four fields; a primary key, "name," "num_visit," and "amount."
Step 2: Survey the table. To make sure the table meets your specifications run a describe command as shown below:
A new table will appear displaying the specified field information. If it is not correct type:
and redo Step 1.
Step 3: Populate the table with data. Use the following code to populate the data:
insert into visits (name,num_visit,amount)values ('M. Smith',1,450);
This example uses four visits from each customer so populate the rest of the fields by repeating this code and altering the data as is necessary. You should have 8 individual statements.
Step 4: Check the populated table. To do this write the following code:
select * from visits;
This will give you a read out in table format of the information entered in the previous step. Of course, your names and quantities may be different.
Step 5 Create the pivot table. This pivot table will execute the four individual visits as column fields and then display how much was spent by each visitor on the corresponding occasion.
select name, sum(amount*(1-abs(sign(num_visit-1)))) as Visit1, sum(amount*(1-abs(sign(num_visit-2)))) as Visit2, sum(amount*(1-abs(sign(num_visit-3)))) as Visit3, sum(amount*(1-abs(sign(num_visit-4)))) as Visit4 from grades group by name;
The only thing that needs to be changed from each line is the number of the visit within the "sign" and the "as" statements. Upon execution, you should see a table print out with the pivoted information.