locked
Cross Tab query RRS feed

  • 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

     

    Monday, August 7, 2006 7:17 AM

Answers

  • 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
    Monday, October 5, 2009 1:32 AM
  • 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
    • Proposed as answer by Melissa Suciadi Monday, October 5, 2009 2:38 AM
    • Marked as answer by Kalman Toth Tuesday, March 6, 2012 6:17 AM
    Monday, October 5, 2009 2:08 AM
  • 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
    • Marked as answer by Kalman Toth Tuesday, March 6, 2012 6:17 AM
    Monday, October 5, 2009 8:24 AM

All replies

  • 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
    Monday, October 5, 2009 1:32 AM
  • 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
    • Proposed as answer by Melissa Suciadi Monday, October 5, 2009 2:38 AM
    • Marked as answer by Kalman Toth Tuesday, March 6, 2012 6:17 AM
    Monday, October 5, 2009 2:08 AM
  • 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
    • Marked as answer by Kalman Toth Tuesday, March 6, 2012 6:17 AM
    Monday, October 5, 2009 8:24 AM