Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Question Unanswered: Dynamic Pivot Table

    What's the best way to create a pivot table dynamically with SQL?
    (Something like MS Access Crosstab)

  2. #2
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    constant number of columns ?

    Do you need the number of columns to dynamically change based on the data retrieved? ... or will the number of columns stay the same?

  3. #3
    Join Date
    Nov 2004
    Posts
    6
    The number of columns needs to change dynamically to fit the data retrieved.

  4. #4
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Crosstab results

    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
    Tom Stone

    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:

    Year Quarter Amount
    1990 1 1.1
    1990 2 1.2
    1990 3 1.3
    1990 4 1.4
    1991 1 2.1
    1991 2 2.2
    1991 3 2.3
    1991 4 2.4

    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,
    sales_pivot b
    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.

  5. #5
    Join Date
    Nov 2004
    Posts
    6

    Thumbs up

    Thanks for your input, it's appreciated.

    I used a similar approach, but I used dynamic SQL to create the pivot table columns using values in the table, then I assigned the values using dynamic SQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •