Hi,
i need a report and i should use pivot table for it.Report will be group by categories .It is not good to use case when statement because there are many categories.u can think Northwind Database as sample and All Categories will be shown as Columns and Report will show customers preference among Categories.I dont know another solution and saw examples as Stored Procedures in Internet for Sql Server.Do u know a Solution except for using case when?
Thanks
It is painful to do row/column swaps in SQL. Each row you want to turn into a column, you have to ask for explicitly. So if you have many categories, your query will be very long, and it'll change every time you add/remove/change a category (this is probably the CASE method you're mentioning). You can write a stored procedure to generate that will generate that query for you (or otherwise build the result set you want), but honestly, in that way lies madness (and probably abysmal performance).
Take the data as rows, like SQL surely wants to give it, and then have your app convert it to columns. That'll be much easier than forcing SQL to do things it doesn't want to do.
You can use Oracle Data Cartridges (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciwhatis.htm) to produce pivot tables more efficiently than case/when but be warned that it's not easy stuff, and I've found the documentation rather sparse in places.
Once you get Oracle 11G there is a built-in PIVOT feature. Prior to that, you are restricted to using CASE (or DECODE) expressions. I have an article on how to automate doing that on my blog.