Access to SQL Server

Moving Access to SQL Server – Pivots

While moving some MS Access Queries to a SQL Server View, I realized that SQL Server handles pivots quite differently than Access.  Unfortunately in SQL Server they aren’t so dynamic as in Access.

Pivot in Access

Quick example of Access:

TRANSFORM SUM(T1.Amount) AS SumOfAmount
SELECT T1.ID, T1.Name, T1.Date
FROM T1
GROUP BY T1.ID, T1.Name, T1.Date
PIVOT T1.AmountType
ID Name Date Cost GST
1 Gameboard 2010-01-01 10.00 1.50

So as you can see, in Access you can basically define anything as an AmountType (in this context you would input this in T1 in the AmountType column), and it would display in your pivot table.  So in the future you could add a Discount type or another Tax type directly into the source table, and the Pivot table would recognize the change.

Pivot in SQL Server

In SQL Server, the columns must be defined in the Pivot query for it to work. If you are using a View, there doesn’t seem to be a dynamic solution, if there is please let me know. Here’s my View:

SELECT     *
FROM         (
SELECT     T1.ID, T1.Name, T1.Date, T1.AmountType, T1.Amount
FROM          T1) AS S
PIVOT (Sum(Amount)
FOR AmountType
IN ([Cost], [GST])) AS P

So, any additional Type you include in your source table must be included in your Pivot table.

Dynamic Pivot in SQL Server

A Dynamic Pivot table can be created, but you have to generate the SQL statement manually and execute it in a Stored Procedure:

DECLARE @SQL NVARCHAR(2000)
DECLARE @QUERY NVARCHAR(4000)

SELECT @SQL = STUFF((select distinct '],[' +AmountType from T1 FOR XML PATH('')), 1, 2, '') + ']'

SET @QUERY = N'SELECT *
FROM (SELECT T1.ID, T1.Name, T1.Date, T1.AmountType, T1.Amount
FROM T1) AS S
PIVOT(Sum(Amount)
FOR AmountType
IN (' + @SQL + ')
) AS P'

EXECUTE (@QUERY)

Here are my sources (many thanks):

Pivots with Dynamic Columns in SQL Server 2005/2008

Pivots with Dynamic Columns in SQL Server 2005

Dynamic Cross-Tabs/Pivot Tables

One thought on “Moving Access to SQL Server – Pivots

Leave a Reply

Your email address will not be published. Required fields are marked *