Here are 2 solutions, but neither are as easy as the MS Access crosstab query.
1. Stuff the selection criteria for the column you want to pivot on into a working table, and then query this table to dynamically build the SQL to retrieve the data, ... then submit the SQL.
2. Create a pivot matrix table to join with the base table. Although this option still has a fixed number of columns, you could orchestrate this solution so that the identity of the columns could change. If you still need to have a variable number of columns, do not read any further. I wrote the following before Sybase had implemented the CASE statement. It could possibly work for you.
Pivoting and Rotating Data
Sometimes it is necessary to rotate (or pivot) results so that columns are presented horizontally and rows are presented vertically. Suppose you have the following normalized data:
Now suppose that you want to rotate the data into a table that contains one row for each year, with the values for each quarter appearing in a separate column:
Year Q1 Q2 Q3 Q4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
You can accomplish this by creating what is referred to as a pivot table:
CREATE TABLE sales_pivot
( Quarter int not null,
Q1 int not null,
Q2 int not null,
Q3 int not null,
Q4 int not null
CREATE unique clustered INDEX sales_pivot_idx1 ON dbo.sales_pivot ( Quarter )
INSERT INTO sales_pivot SELECT 1, 1, 0, 0, 0
INSERT INTO sales_pivot SELECT 2, 0, 1, 0, 0
INSERT INTO sales_pivot SELECT 3, 0, 0, 1, 0
INSERT INTO sales_pivot SELECT 4, 0, 0, 0, 1
The following single SELECT statement will then efficiently produce the results you are looking for:
SELECT a.Year ,
Q1 = SUM( b.Q1 * a.Amount ) ,
Q2 = SUM( b.Q2 * a.Amount ) ,
Q3 = SUM( b.Q3 * a.Amount ) ,
Q4 = SUM( b.Q4 * a.Amount )
FROM sales a,
WHERE a.Quarter = b.Quarter
GROUP BY a.Year
The column vectors of quarterly data for each year are multiplied by the diagonal pivot matrix (a join is similar to matrix multiplication), giving a row vector for that year. The GROUP BY statement compresses the four rows for each year into a single row. This approach was published by Steve Roti in the August 1990 issue of DBMS Magazine. Although there is a crosstab report feature within PowerBuilder, it has been reported to be rather slow. Hence, this technique has been especially beneficial and time saving to PowerBuilder developers creating crosstab reports with many columns.