PIVOT question

PIVOT question

• phatrice93

  To do this in 2000, check out my reply of this post:

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=249991&SiteID=1

  However, the PIVOT method in 2005 is better. What errors are you getting

  
  

• Mark Panarusky

  Yes, the use cases of PIVOT operator is very restrictive right now. If you want to pivot on say multiple measures or aggregates then you have to use the traditional SQL approach of CASE expressions and GROUP BY in the query. The dynamic list generation for PIVOT operator is also something we have heard frequently from customers. A future version of SQL Server might provide additional enhancements, optimizations for PIVOT which will improve the usage & performance of the query. So if you find instances where you can achieve the result using PIVOT operator then use it instead of the CASE / GROUP BY approach. Hope this clarifies.
  
  

• giorgos

  i am using sql2005, ive looked up the documentation on the PIVOT function but still am having diffaculity in applying it
  
  

• Hussain_Alyousif

  are you using SQL 2000 or 2005
  
  If 2005, use the pivot function
  
  
  

• Sankar Reddy

  You can do something like below using PIVOT operator:

  select p.yr as Year, p.[1] as [Jan], p.[2] as [Feb], ....

  from (select year(receivedate) as yr, month(receivedate) as mn, trancnt from tbl) as t

  pivot (sum(t.trancnt) for t.mn in ([1], [2], [3], ...)) as p

  
  
  

• DrMicrochip

  I am curious to know what are the practical uses of the Pivot function assuming you do not grant end users acces to your database.

  On my side, I use the pivot function to pack more data in Reporting Services while avoiding the Matrix reports shortcomings, however it is very tedious to build. If you want if maitenance free. You have to break the rule of "NO dynamic SQL ever".

  Any thoughts

  Philippe

  
  
• PIVOT question