How to Pivot this query (1 Viewer)

[majhl]

Hello,

I'm trying various things in T-SQL and one of them is the 'pivot' function. What I'm trying to get is data which has the Year as a row in a query, the months as columns and the quantity as a value, in much the same way that you'd be able to do with Access' crosstab query, so:

.......Jan, Feb, Mar....
1996 59 69 .....

So far, I've managed to get all the correct format for the output I need, but have no idea how to use 'pivot' to achieve what I want - if it is even possible as I've been reading conflicting things about SQL Server's pivot function. Can someone help out?

TIA.

use northwind

go

SELECT year(orderdate) as OrderYear, 
datename(m, orderdate) as OrderMonth,
count(Quantity) as QOrdered

FROM orders 

INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID 

group by year(orderdate), month(orderdate), datename(m, orderdate)

order by year(orderdate) Asc, month(orderdate) asc

 

[majhl]

To answer my own question:

I've found a method called 'The Rozenshtein Method' which does exactly what I want. It's documented here:

http://www.stephenforte.net/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8