Support for a sequence of elements is often needed to solve various SQL problems. For example, given a range of dates, you may wish to generate one row for each date in the range. Or, you may wish to translate a series of values returned in separate rows into a series of values in separate columns of the same row. To implement such functionality, you can use a permanent table that stores a series of sequential numbers. Such a table is referred to as a Pivot table.
CREATE TABLE Pivot ( i INT, PRIMARY KEY(i) ) CREATE TABLE Foo( i CHAR(1) )
The Foo table is a simple support table into which you should insert the following 10 rows:
INSERT INTO Foo VALUES('0') INSERT INTO Foo VALUES('1') INSERT INTO Foo VALUES('2') INSERT INTO Foo VALUES('3') INSERT INTO Foo VALUES('4') INSERT INTO Foo VALUES('5') INSERT INTO Foo VALUES('6') INSERT INTO Foo VALUES('7') INSERT INTO Foo VALUES('8') INSERT INTO Foo VALUES('9')
INSERT INTO Pivot SELECT f1.i+f2.i+f3.i FROM Foo f1, Foo F2, Foo f3
If you list the rows of Pivot table, you'll see that it has the desired number of elements and that they will be numbered from 0 through 999.
As you'll see in recipes that follow in this book, the Pivot table is often used to add a sequencing property to a query. Some form of Pivot table is found in many SQL-based systems, though it is often hidden from the user and used primarily within predefined queries and procedures.
You've seen how the number of table joins (of the Foo table) controls the number of rows that our INSERT statement generates for the Pivot table. The values from 0 through 999 are generated by concatenating strings. The digit values in Foo are character strings. Thus, when the plus (+) operator is used to concatenate them, we get results such as the following:
'0' + '0' + '0' = '000' '0' + '0' + '1' = '001' ...
These results are inserted into the INTEGER column in the destination Pivot table. When you use an INSERT statement to insert strings into an INTEGER column, the database implicitly converts those strings into integers. The Cartesian product of the Foo instances ensures that all possible combinations are generated, and, therefore, that all possible values from 0 through 999 are generated.
It is worthwhile pointing out that this example uses rows from 0 to
999 and no negative numbers. You could easily generate negative
numbers, if required, by repeating the INSERT statement with the
"-" sign in front of the
concatenated string and being a bit careful about the 0 row.
There's no such thing as a -0, so you
wouldn't want to insert the
row when generating negative Pivot numbers. If you did so,
you'd end up with two 0 rows in your Pivot table. In
our case, two 0 rows are not possible, because we define a primary
key for our Pivot table.
The Pivot table is probably the most useful table in the SQL world. Once you get used to it, it is almost impossible to create a serious SQL application without it. As a demonstration, let us use the Pivot table to generate an ASCII chart quickly from the code 32 through 126:
SELECT i Ascii_Code, CHAR(i) Ascii_Char FROM Pivot WHERE i BETWEEN 32 AND 126 Ascii_Code Ascii_Char ----------- ---------- 32 33 ! 34 " 35 # 36 $ 37 % 38 & 39 ' 40 ( 41 ) 42 * 43 + 44 , 45 - 46 . 47 / 48 0 49 1 50 2 51 3 ...
What's great about the use of the Pivot table in this particular instance is that you generated rows of output without having an equal number of rows of input. Without the Pivot table, this is a difficult, if not impossible, task. Simply by specifying a range and then selecting Pivot rows based on that range, we were able to generate data that doesn't exist in any database table.
You must have enough Pivot table rows to accommodate the range that
you specify. Had we used
BETWEEN 32 AND 2000, our
query would have failed, because our Pivot table has only 1,000 rows,
not the 2,001 that would be required by such a large range.
SELECT CONVERT(CHAR(10),DATEADD(d,i,CURRENT_TIMESTAMP), 121) date, DATENAME(dw,DATEADD(d,i,CURRENT_TIMESTAMP)) day FROM Pivot WHERE i BETWEEN 0 AND 6 date day ---------- ------------------------------ 2001-11-05 Monday 2001-11-06 Tuesday 2001-11-07 Wednesday 2001-11-08 Thursday 2001-11-09 Friday 2001-11-10 Saturday 2001-11-11 Sunday
These two queries are just quick teasers, listed here to show you how a Pivot table can be used in SQL. As you'll see in other recipes, the Pivot table is often an indispensable tool for quick and efficient problem solving.