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!there's probably a better solution out there, but this will get you started:

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'|||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|||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).|||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...

| A | B | C
------
i | 10 | 5 | 2
------
ii | 20 | 7 | 4
------
iii | 30 | 9 | 6
------|||this is exactly what i was talking about in the previous post!|||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/default.asp?url=/library/en-us/olapdmad/agcubesintro_80qb.asp|||Thanx for the advice! Appreciate your time!