Cross Tab query

Question

Is there any simple cross tab query template, which can used by general user?

I need it so that I can provide it to our Power users, so that they can use it themselves, instead of coming back to the IT Department.

(something similar like the one found in the Access)

Tomal

 

Answer 1

Hello

The syntax for PIVOT is below.  The syntax is a bit yucky, hopefully in the next version of SQL Server they release an easy to use dynamic pivot.  If this is too difficult for your power users I recommend creating a SSRS Report Model so users can use Report Builder to easily create pivots (with an interface instead of syntax).

WITH BaseQuery AS (
SELECT [Total Project Amount], YEAR([Date Requested]) AS YearRequested, MONTH([Date Requested]) AS MonthRequested FROM tblP21Amendments2
)
SELECT YearRequested, [1] 'Jan', [2] 'Feb', [3] 'Mar', [4] 'Apr', [5] 'May', [6] 'Jun', [7] 'Jul', [8] 'Aug', [9] 'Sep', [10] 'Oct', [11] 'Nov', [12] 'Dec'
FROM BaseQuery
PIVOT(SUM([Total Project Amount]) FOR MonthRequested IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotQuery

Answer 2

You can find many pivoting examples online, maybe best to look at the official BOL article which provides the syntax and some examples:
http://technet.microsoft.com/en-us/library/ms177410.aspx

But if you are targeting not IT users, perhaps a better approach is to point them to use some reporting tool. For example, Excel has very good capabilities to populate data from data source and then to pivot. Similar with reporting tools like Crystal Reports and SSRS.

---

Plamen Ratchev

Answer 3

Here is a simple Dynamic PIVOT crosstab query which can be used as template.
If you really want to please your power user though, you give them OLAP cubes with Report Builder 2.0.

/*******
* Dynamic PIVOT template
********/

USE AdventureWorks 
GO 

DECLARE  @DynamicSQL  AS NVARCHAR(MAX) 
-- SQL pivot list generation dynamically -  Dynamic pivot list  
DECLARE  @ReportColumnNames  AS NVARCHAR(MAX)

SELECT @ReportColumnNames = Stuff((SELECT   ', ' + QUOTENAME(YYYY) AS [text()] 
          FROM     (SELECT DISTINCT YYYY = CAST(Year(OrderDate) AS VARCHAR) 
                    FROM   Sales.SalesOrderHeader) x 
          ORDER BY YYYY 
          FOR XML PATH ('')),1,1,'') 

PRINT @ReportColumnNames 
-- [2001],[2002],[2003],[2004] 

SET @DynamicSQL = N'SELECT * FROM (SELECT [Store]=s.Name,     
YEAR(OrderDate) AS OrderYear,     
Freight = convert(money,convert(varchar, Freight))     
FROM Sales.SalesOrderHeader soh  
JOIN Sales.Store s     
ON soh.CustomerID = s.CustomerID) as Header     
PIVOT (SUM(Freight)    FOR OrderYear 
IN(' + @ReportColumnNames + N'))  AS Pvt ORDER BY 1' 

PRINT @DynamicSQL -- Testing & debugging  
/*
SELECT * FROM (SELECT [Store]=s.Name,     YEAR(OrderDate) AS OrderYear,     Freight = convert(money,convert(varchar, Freight))     FROM Sales.SalesOrderHeader soh  JOIN Sales.Store s     ON soh.CustomerID = s.CustomerID) as Header     PIVOT (SUM(Freight)    FOR OrderYear IN( [2001], [2002], [2003], [2004]))  AS Pvt ORDER BY 1

*/
-- Execute dynamic sql 
EXEC sp_executesql   @DynamicSQL 
GO
/* Partial results
Store	2001	2002	2003	2004
A Bike Store	921.55	1637.24	NULL	NULL
A Great Bicycle Company	142.08	114.34	15.24	NULL
A Typical Bike Shop	976.61	1529.08	NULL	NULL
Acceptable Sales & Service	12.58	25.17	NULL	NULL
Accessories Network	NULL	NULL	24.72	43.06
Acclaimed Bicycle Company	NULL	NULL	190.01	53.80
*/ 

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com