The pivot table provides support for a sequence of values for ex: In case you want to have sequential values from 1 to 100 . These sequential values are required and can be used in numerous cases. In pivot tables values are not inserted directly but they are inserted inside a support table which is then concatenated with itself to populate pivot tables.
How to create a pivot table ?
To create a pivot table and insert values into it, you would have to create 2 following tables:-
1) Pivot table 2) Temporary/Support table
1) Pivot table: It has a pretty simple structure. To create a pivot table in sql execute the following query
create table if not exists pivot (
c int,
primary key (c))
The primary key on c is taken so that no repetitive value are entered.
2) Support table:- The next step is to create a support/ temporary table which will be used to fill up the pivot table. To create a support table execute the following simple query.
create table if not exists temp (
ch char(1))
the field is taken as char so that it can be concatenated with itself easily. For ex: the character concatenation operation '1'+'1'= '11'.
Then we have to insert 10 values (0 through 9) into the temp table. So execute the following insert statements one at a time.
insert into temp values('0')
insert into temp values('1')
insert into temp values('2')
insert into temp values('3')
insert into temp values('4')
insert into temp values('5')
insert into temp values('6')
insert into temp values('7')
insert into temp values('8')
insert into temp values('9')
3. Inserting values into the pivot table:- Now using 10 rows of the temp table, to generate 100 rows of pivot table all you have to do is concatenate temp to itself 2 times. The concatenation operation was explained above.
Execute the following query, if you are using microsoft sql:-
Else if you are using mysql execute the following query:-Insert into pivot
select t1.ch+t2.ch
from temp t1, temp t2
The query will produce elements from 0 through 99.Insert into pivot
select concat(t1.ch,t2.ch)
from temp t1, temp t2
Example usage of pivot table:-
As a simple example you can use it to print ascii chart of the ascii characters from 48 through 96. In this case the query would be as shown in the following code box:-
select c ascii_code, char(c) ascii_value
from pivot
where c between 48 and 96
