Results 1 to 8 of 8

Thread: Pivot Tables

  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Angry Unanswered: Pivot Tables

    Greetings, can anyone tell me how to create a pivot table view? (If at all) I want to manipluate data to a view to use in Crystal reports. Current table has producta "A", "B" & "C" with three options "i", "ii" & "iii" each with amounts per product & options:

    A i 10
    A ii 20
    A iii 30
    B i 5
    B ii 7
    B iii 9
    C i 2
    C ii 4
    C iii 6

    I want to create a view that lists the product "A" and Options "i" in the rows, with Colums "i", "ii" and "iii" and their corresponding amounts listed in the relevant colums. Thanx!

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    there's probably a better solution out there, but this will get you started:

    Code:
    declare @tbl table (
       product char(1) not null, options char(3) not null, price money not null)
    insert @tbl values ('A', 'i', 10)
    insert @tbl values ('A', 'ii', 20)
    insert @tbl values ('A', 'iii', 30)
    insert @tbl values ('B', 'i', 5)
    insert @tbl values ('B', 'ii', 7)
    insert @tbl values ('B', 'iii', 9)
    insert @tbl values ('C', 'i', 2)
    insert @tbl values ('C', 'ii', 4) 
    insert @tbl values ('C', 'iii', 6)
    
    select p.product, [i]=t1.price, [ii]=t2.price, [iii]=t3.price
    from (select distinct product from @tbl) p
    inner join @tbl t1
       on p.product = t1.product and t1.options = 'i'
    inner join @tbl t2
       on p.product = t2.product and t2.options = 'ii'
    inner join @tbl t3
       on p.product = t3.product and t3.options = 'iii'

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It sounds like you are trying to create a CROSSTAB query, and you can look up how to do it using CASE statements by searching Books Online for keyword "crosstab".

    However, this will NOT give you a try pivot table report, which is dynamically configurable by the user. I'm not sure that Crystal can even do this. As a matter of fact, for a pivot table you don't want oto have your data in crosstab format. If you truly want pivot functionality and the ability to slice, dice, and summarize your data dynamically, create a pivot table in Excel or in an ASP page that links to the data in your table, (through a view, preferably), and leave your data in its current columnar format.

    blindman

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    actually crystal was able to do it even when it was part of vb4.0

    but i still think that having this static approach is better than allowing users to build pivots dynamically, because if they transpose columns and rows (intentionally or not) the whole thing will croke (or may croke).

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Would this not be equivilent to a cube? It seems that A, B, and C would be one dimension, and i, ii, and iii would be a second dimension, and the values would be the intersection points of the the two dimensions....

    Code:
        | A  | B | C
    ----------------
    i   | 10 | 5 | 2
    ----------------
    ii  | 20 | 7 | 4
    ----------------
    iii | 30 | 9 | 6
    ----------------
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    this is exactly what i was talking about in the previous post!

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by ms_sql_dba
    this is exactly what i was talking about in the previous post!
    Bah.. sorry.. I started typing, and got side tracked for a little while and didn't get to finish my post until there had been 3 other replies!

    SQL supports the creation of cubes if you didn't want to do a bunch of joins.. that might get costly if you had lots of dimensions...

    http://msdn.microsoft.com/library/de...intro_80qb.asp
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Oct 2003
    Posts
    2

    Pivot Tables

    Thanx for the advice! Appreciate your time!

Posting Permissions

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