Skip to main content

Posts

Showing posts from July, 2011

MS SQL Pivot Tables

Somebody at work asked me today, "Ankeet it is hard to understand/remember how to use PIVOT tables in sql. Can you give me an example?". So here it is. I have used table name prefixes so as to make the code easier to understand and be able to identify where does each field come from. I rarely use pivot tables in sql. use dbName go SELECT myPivotTable.CustomerID, isnull(myPivotTable.[2003],0) as [2003 ActualPaid], isnull(myPivotTable.[2004],0) as [2004 ActualPaid] FROM ( select phf.CustomerID, phf.SystemYear, phf.ActualPaid from Sales as phf where phf.CustomerID between 33131 and 33250 ) AS mySourceTable PIVOT ( avg(mySourceTable.ActualPaid) for mySourceTable.SystemYear in ([2003],[2004]) ) AS myPivotTable ORDER BY myPivotTable.CustomerID