Transact-SQL Cookbook

Chapter 1. Pivot Tables

Using a Pivot Table

Problem

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.

Many of the recipes in our book use a Pivot table, and, in all cases, the table’s name is Pivot. This recipe shows you how to create that table.

Solution

First, create the Pivot table. Next, create a table named Foo that will help you populate the 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')

Using the 10 rows in the Foo table, you can easily populate the Pivot table with 1,000 rows. To get 1,000 rows from 10 rows, join Foo to itself three times to create a Cartesian product:

INSERT INTO Pivot
   SELECT f1.i+f2.i+f3.i
   FROM Foo f1, Foo F2, Foo f3

If you list the rows of ...