PIVOT question

I have a basic table consisting of several thousand records. Im trying to generate a pivot query for a report. the table consists of records, each of which has a recieved date ( small date time ) and a tranactioncount ( int ) . Im looking to generate a PIVOT to show the show the month and year counts for the files recieved

ie

YEAR | JAN FEB MAR APR etc

2004 ! 2 34 67 43

2005 | 12 2 3 1

can anybody explain in laymans terms how to do this

Thank you in advance

are you using SQL 2000 or 2005 ?

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

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?

|||

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

|||

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

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