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:
[sql]TRANSFORM SUM(T1.Amount) AS SumOfAmountSELECT T1.ID, T1.Name, T1.Date
FROM T1
GROUP BY T1.ID, T1.Name, T1.Date
PIVOT T1.AmountType[/sql]
| 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:
[sql]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[/sql]
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:
[sql]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)
[/sql]
Here are my sources (many thanks):
nice