none
Is this possible? Crosstab query RRS feed

  • Question

  • Hi All,

    I have a rather complicated requirement am trying to fulfill. I'll make it simple and leave unnecessary details out. I have a table with structure similar to shown below.

    DECLARE @Trans TABLE (
    	trans_year int NULL,
    	customer_name nvarchar(100) NULL,
    	Amount FLOAT,
    	Quantity FLOAT)
    
    INSERT INTO @Trans VALUES (2010, 'ABC', 100, 200)
    INSERT INTO @Trans VALUES (2011, 'ABC', 200, 100)
    INSERT INTO @Trans VALUES (2012, 'ABC', 500, 100)
    INSERT INTO @Trans VALUES (2013, 'ABC', 400, 700)
    INSERT INTO @Trans VALUES (2014, 'ABC', 600, 800)
    
    SELECT * FROM @Trans

    What I am trying to do is, for each customer, get all the amounts across by year. And for each year, I need to add a field that can show the total until that year(sort of like YTD). This code will be executed in a stored procedure and the stored procedure has two parameters called start_year and end_year. For the first year provided as the parameter, we don't need the Total column as it is going to be same as the amount for that year. Also, the quantity column is going to act like a normal column(not shown in the desired output below) and can be ignored for our discussion. Below is how the output needs to be.

    Can someone please help? Thanks in advance!!

    Cust 2010 2011 Total  2012 Total  2013 Total  2014 Total 
     ABC 100 200 300 500 800 400 1200 600 1800

    Monday, January 20, 2014 7:12 AM

Answers

  • As visakh pointed  you would need to use dynamic SQL to generate dates

    -- Dynamic PIVOT
    DECLARE
      @cols AS NVARCHAR(MAX),
      @y    AS INT,
      @sql  AS NVARCHAR(MAX);

    -- Construct the column list for the IN clause
    -- e.g., [2010],[2011],[2012]
    SET @cols = STUFF(
      (SELECT N',' + QUOTENAME(y) AS [text()]
       FROM (SELECT DISTINCT YEAR(orderdate) AS y FROM dbo.Table) AS Y
       ORDER BY y
       FOR XML PATH('')),
      1, 1, N'');

    -- Construct the full T-SQL statement
    -- and execute dynamically
    SET @sql = N'SELECT *
    FROM (SELECT custome_name, YEAR(orderdate) AS orderyear, qty
          FROM dbo.Table) AS D
      PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P;';

    EXEC sp_executesql @sql;
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, January 20, 2014 12:49 PM
    Answerer
  • Well, to fix your immediate problem you needed to use correct variable in the loop (@year).

    So, this is your code corrected

    CREATE TABLE #Trans (
    	trans_year INT NULL
    	,customer_name NVARCHAR(100) NULL
    	,Amount FLOAT
    	,Quantity FLOAT
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2010
    	,'ABC'
    	,100
    	,200
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2011
    	,'ABC'
    	,200
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2012
    	,'ABC'
    	,500
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2013
    	,'ABC'
    	,400
    	,700
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2014
    	,'ABC'
    	,600
    	,800
    	)
    
    DECLARE @StartYear INT
    	,@EndYear INT;
    
    SELECT @StartYear = MIN(trans_year)
    	,@EndYear = MAX(trans_year)
    FROM #Trans;
    
    DECLARE @Columns NVARCHAR(max)
    	,@Year INT;
    
    SET @Columns = '';
    SET @Year = @StartYear
    
    WHILE @Year <= @EndYear
    BEGIN
    	SET @Columns = @Columns + ', ' + quotename(cast(@year AS NVARCHAR(max)))
    	SET @Year = @Year + 1;
    END
    
    SET @Columns = STUFF(@Columns, 1, 2, '');
    
    DECLARE @SQL NVARCHAR(max);
    
    SET @SQL = ';WITH CTE AS (SELECT trans_year, customer_name, Amount FROM #Trans
    
    WHERE trans_year  >= @StartYear and trans_year < @EndYear)  
    
    SELECT * FROM CTE PIVOT (SUM(Amount) FOR trans_year IN (' + @Columns + ')) pvt'
    
    --print @SQL;
    EXECUTE sp_ExecuteSQL @SQL
    	,N'@StartYear INT, @EndYear INT'
    	,@StartYear
    	,@EndYear
    
    DROP TABLE #Trans

    However, this is not exactly what I had in mind for your case. In your case since you wanted to add Total columns in between I proposed to use slightly different approach of using CASE expressions and GROUP BY.

    Will you be able to figure this out now or you need the solution spelled out?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by SnowShine429 Monday, January 27, 2014 10:57 PM
    Monday, January 20, 2014 6:23 PM
    Moderator
  • Here is what I had in mind for your case:

    CREATE TABLE #Trans (
    	trans_year INT NULL
    	,customer_name NVARCHAR(100) NULL
    	,Amount FLOAT
    	,Quantity FLOAT
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2010
    	,'ABC'
    	,100
    	,200
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2011
    	,'ABC'
    	,200
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2012
    	,'ABC'
    	,500
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2013
    	,'ABC'
    	,400
    	,700
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2014
    	,'ABC'
    	,600
    	,800
    	)
    
    DECLARE @StartYear INT
    	,@EndYear INT;
    
    SELECT @StartYear = MIN(trans_year)
    	,@EndYear = MAX(trans_year)
    FROM #Trans;
    
    DECLARE @Columns NVARCHAR(max)
    	,@Year INT;
    
    SET @Columns = '';
    SET @Year = @StartYear
    
    WHILE @Year <= @EndYear
    BEGIN
    	SET @Columns = @Columns + N',
    	 SUM(CASE WHEN trans_year = ' + CAST(@year AS NVARCHAR(max)) + ' THEN Amount END) AS ' + quotename(cast(@year AS NVARCHAR(max))) + CASE 
    			WHEN @Year > @StartYear
    				THEN N',
    	 SUM(CASE WHEN trans_year <= ' + CAST(@year AS NVARCHAR(max)) + ' THEN Amount END) AS ' + quotename('Total up to ' + cast(@year AS NVARCHAR(max)))
    			ELSE ''
    			END;
    	SET @Year = @Year + 1;
    END
    
    SET @Columns = STUFF(@Columns, 1, 2, '');
    
    DECLARE @SQL NVARCHAR(max);
    
    SET @SQL = ';WITH CTE AS (SELECT trans_year, customer_name, Amount FROM #Trans
    
    WHERE trans_year  >= @StartYear and trans_year <= @EndYear)  
    
    SELECT Customer_Name, ' + @Columns + '
     FROM cte GROUP BY Customer_Name'
    
    PRINT @SQL;
    
    EXECUTE sp_ExecuteSQL @SQL
    	,N'@StartYear INT, @EndYear INT'
    	,@StartYear
    	,@EndYear
    
    DROP TABLE #Trans;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by SnowShine429 Monday, January 27, 2014 10:56 PM
    Monday, January 20, 2014 6:33 PM
    Moderator

All replies

  • SnowShine429,

    Are your Years fixed. That is do you know the minimum value for the year attribute.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, January 20, 2014 7:21 AM
  • Take a look at dynamic PIVOT:

    http://www.sqlusa.com/bestpractices2005/dynamicpivot/

    Also SSRS has built-in dynamic columns.


    Kalman Toth Database & OLAP Architect SELECT Query Video Tutorial 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, January 20, 2014 7:32 AM
    Moderator
  • SnowShine429,

    Try this and then you can look for the dynamic pivot option(Google it).

    CREATE TABLE #Trans(
    	trans_year int NULL,
    	customer_name nvarchar(100) NULL,
    	Amount FLOAT,
    	Quantity FLOAT)
    
    INSERT INTO #Trans VALUES (2010, 'ABC', 100, 200)
    INSERT INTO #Trans VALUES (2011, 'ABC', 200, 100)
    INSERT INTO #Trans VALUES (2012, 'ABC', 500, 100)
    INSERT INTO #Trans VALUES (2013, 'ABC', 400, 700)
    INSERT INTO #Trans VALUES (2014, 'ABC', 600, 800)
    
    
    GO
    CREATE PROCEDURE TechNetForum_SnowShine429 
    		@StartDate	INT,
    		@EndDate	INT
    AS
    SELECT customer_name,trans_year,Amount,
    		(SELECT SUM(Amount) 
    		FROM #Trans Inn 
    		WHERE	Inn.customer_name=Main.customer_name 
    				AND Inn.trans_year<=Main.trans_year)
    FROM #Trans Main
    WHERE trans_year>=@StartDate AND trans_year<=@EndDate
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, January 20, 2014 7:36 AM
  • SELECT customer_name,
    MAX(CASE WHEN trans_year=2010 THEN  Amount END ) [2010],
    MAX(CASE WHEN trans_year=2011 THEN  Amount END ) [2011],
    SUM(CASE WHEN trans_year IN (2010,2011) THEN Amount END) [Total_2010-2011],
    MAX(CASE WHEN trans_year=2012 THEN  Amount END ) [2012],
    MAX(CASE WHEN trans_year=2013 THEN  Amount END ) [2013],
    MAX(CASE WHEN trans_year=2014 THEN  Amount END ) [2014]
    FROM @Trans
    GROUP BY customer_name

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by EitanBlumin Monday, January 20, 2014 8:38 AM
    Monday, January 20, 2014 7:51 AM
    Answerer
  • SELECT customer_name,
    MAX(CASE WHEN trans_year=2010 THEN  Amount END ) [2010],
    MAX(CASE WHEN trans_year=2011 THEN  Amount END ) [2011],
    SUM(CASE WHEN trans_year IN (2010,2011) THEN Amount END) [Total_2010-2011],
    MAX(CASE WHEN trans_year=2012 THEN  Amount END ) [2012],
    MAX(CASE WHEN trans_year=2013 THEN  Amount END ) [2013],
    MAX(CASE WHEN trans_year=2014 THEN  Amount END ) [2014]
    FROM @Trans
    GROUP BY customer_name

    What if user doesn't know how many years are there. Or lets say It contains data for 2 decades. In that case writing this statement is not suitable.

    If I am wrong please revert back.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, January 20, 2014 11:53 AM
  • You can generate Year list and use it for dynamic pivot

    see these links

    http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

    If you want to crosstab multiple columns like totals in between values then use this

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Visakh16MVP Monday, January 20, 2014 12:56 PM
    Monday, January 20, 2014 12:06 PM
  • As visakh pointed  you would need to use dynamic SQL to generate dates

    -- Dynamic PIVOT
    DECLARE
      @cols AS NVARCHAR(MAX),
      @y    AS INT,
      @sql  AS NVARCHAR(MAX);

    -- Construct the column list for the IN clause
    -- e.g., [2010],[2011],[2012]
    SET @cols = STUFF(
      (SELECT N',' + QUOTENAME(y) AS [text()]
       FROM (SELECT DISTINCT YEAR(orderdate) AS y FROM dbo.Table) AS Y
       ORDER BY y
       FOR XML PATH('')),
      1, 1, N'');

    -- Construct the full T-SQL statement
    -- and execute dynamically
    SET @sql = N'SELECT *
    FROM (SELECT custome_name, YEAR(orderdate) AS orderyear, qty
          FROM dbo.Table) AS D
      PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P;';

    EXEC sp_executesql @sql;
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, January 20, 2014 12:49 PM
    Answerer
  • Thank you all for your response.

    The problem is not about getting the columns across, I can figure out how to get the extra column to show to sum of all the amount until that year. Here is what I have so far. Can anyone please help? Thanks in advance.

    CREATE TABLE #Trans(
    	trans_year int NULL,
    	customer_name nvarchar(100) NULL,
    	Amount FLOAT,
    	Quantity FLOAT)
    
    INSERT INTO #Trans VALUES (2010, 'ABC', 100, 200)
    INSERT INTO #Trans VALUES (2011, 'ABC', 200, 100)
    INSERT INTO #Trans VALUES (2012, 'ABC', 500, 100)
    INSERT INTO #Trans VALUES (2013, 'ABC', 400, 700)
    INSERT INTO #Trans VALUES (2014, 'ABC', 600, 800)
    
    -- Dynamic PIVOT
    DECLARE
      @cols AS NVARCHAR(MAX),
      @y    AS INT,
      @sql  AS NVARCHAR(MAX);
    
    -- Construct the column list for the IN clause
    -- e.g., [2010],[2011],[2012]
    SET @cols = STUFF(
      (SELECT N',' + QUOTENAME(y) AS [text()]
       FROM (SELECT DISTINCT trans_year AS y FROM #Trans) AS Y
       ORDER BY y
       FOR XML PATH('')),
      1, 1, N'');
    
    -- Construct the full T-SQL statement
    -- and execute dynamically
    SET @sql = N'SELECT *
    FROM (SELECT customer_name, trans_year AS orderyear, Amount
          FROM #Trans) AS D
      PIVOT(SUM(Amount) FOR orderyear IN(' + @cols + N')) AS P;';
    
    EXEC sp_executesql @sql;
    GO
    DROP TABLE #Trans

    Monday, January 20, 2014 4:00 PM
  • Take a look at earlier Uri's idea and this featured article

    http://social.technet.microsoft.com/wiki/contents/articles/21135.t-sql-create-report-for-last-10-years-of-data.aspx

    I suggest constructing your dynamic SQL in a simple loop rather than using XML approach.

    For you case your expressions will be

    SUM(case when year = 2010 then Amount end) as 2010,

    SUM(case when year <=2010 then Amount end) as [Total up to 2010]

    etc.

    Just generate such expressions in a loop and then execute the dynamic SQL.

    I am not showing you the exact solution, but the article provided has all the code that you need to write it yourself.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 20, 2014 5:06 PM
    Moderator
  • thank you Naomi, I tried the code you suggested but getting the error. can you please help?

    Msg 207, Level 16, State 1, Line 5

    Invalid column name 'trans_year'.

    Msg 8156, Level 16, State 1, Line 5

    The column '2010' was specified multiple times for 'pvt'.

    CREATE TABLE #Trans  (
    	trans_year int NULL,
    	customer_name nvarchar(100) NULL,
    	Amount FLOAT,
    	Quantity FLOAT)
    
    INSERT INTO #Trans VALUES (2010, 'ABC', 100, 200)
    INSERT INTO #Trans VALUES (2011, 'ABC', 200, 100)
    INSERT INTO #Trans VALUES (2012, 'ABC', 500, 100)
    INSERT INTO #Trans VALUES (2013, 'ABC', 400, 700)
    INSERT INTO #Trans VALUES (2014, 'ABC', 600, 800)
    
    DECLARE @StartYear INT ,@EndYear INT  
    
    SELECT  @StartYear = MIN(trans_year) FROM #Trans
    SELECT  @EndYear = MAX(trans_year) FROM #Trans
    
    
    
    DECLARE @Columns NVARCHAR(max), @Year INT;
      
    
    SET @Columns = '';
    
    SET @Year  = @StartYear
      
    
    WHILE @StartYear <= @EndYear
    
    BEGIN
    
        SET @Columns = @Columns + ', ' + quotename(cast(@year AS  NVARCHAR(max)))
    
        SET @StartYear  = @StartYear + 1;
    
    END  
    
    SET @Columns = STUFF(@Columns, 1, 2, '');
    
    DECLARE @SQL nvarchar(max);
    
    
    SET @SQL = ';WITH CTE AS (SELECT trans_year customer_name, Amount, Quantity FROM #Trans
    
    WHERE trans_year  >= @StartYear and trans_year < @EndYear)  
    
    SELECT * FROM CTE PIVOT (SUM(Amount) FOR trans_year IN (' + @Columns + ')) pvt'
      
    
    EXECUTE  sp_ExecuteSQL @SQL, N'@StartYear INT, @EndYear INT', @StartYear, @EndYear
    
    DROP TABLE #Trans

    Monday, January 20, 2014 5:44 PM
  • Well, to fix your immediate problem you needed to use correct variable in the loop (@year).

    So, this is your code corrected

    CREATE TABLE #Trans (
    	trans_year INT NULL
    	,customer_name NVARCHAR(100) NULL
    	,Amount FLOAT
    	,Quantity FLOAT
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2010
    	,'ABC'
    	,100
    	,200
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2011
    	,'ABC'
    	,200
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2012
    	,'ABC'
    	,500
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2013
    	,'ABC'
    	,400
    	,700
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2014
    	,'ABC'
    	,600
    	,800
    	)
    
    DECLARE @StartYear INT
    	,@EndYear INT;
    
    SELECT @StartYear = MIN(trans_year)
    	,@EndYear = MAX(trans_year)
    FROM #Trans;
    
    DECLARE @Columns NVARCHAR(max)
    	,@Year INT;
    
    SET @Columns = '';
    SET @Year = @StartYear
    
    WHILE @Year <= @EndYear
    BEGIN
    	SET @Columns = @Columns + ', ' + quotename(cast(@year AS NVARCHAR(max)))
    	SET @Year = @Year + 1;
    END
    
    SET @Columns = STUFF(@Columns, 1, 2, '');
    
    DECLARE @SQL NVARCHAR(max);
    
    SET @SQL = ';WITH CTE AS (SELECT trans_year, customer_name, Amount FROM #Trans
    
    WHERE trans_year  >= @StartYear and trans_year < @EndYear)  
    
    SELECT * FROM CTE PIVOT (SUM(Amount) FOR trans_year IN (' + @Columns + ')) pvt'
    
    --print @SQL;
    EXECUTE sp_ExecuteSQL @SQL
    	,N'@StartYear INT, @EndYear INT'
    	,@StartYear
    	,@EndYear
    
    DROP TABLE #Trans

    However, this is not exactly what I had in mind for your case. In your case since you wanted to add Total columns in between I proposed to use slightly different approach of using CASE expressions and GROUP BY.

    Will you be able to figure this out now or you need the solution spelled out?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by SnowShine429 Monday, January 27, 2014 10:57 PM
    Monday, January 20, 2014 6:23 PM
    Moderator
  • Here is what I had in mind for your case:

    CREATE TABLE #Trans (
    	trans_year INT NULL
    	,customer_name NVARCHAR(100) NULL
    	,Amount FLOAT
    	,Quantity FLOAT
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2010
    	,'ABC'
    	,100
    	,200
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2011
    	,'ABC'
    	,200
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2012
    	,'ABC'
    	,500
    	,100
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2013
    	,'ABC'
    	,400
    	,700
    	)
    
    INSERT INTO #Trans
    VALUES (
    	2014
    	,'ABC'
    	,600
    	,800
    	)
    
    DECLARE @StartYear INT
    	,@EndYear INT;
    
    SELECT @StartYear = MIN(trans_year)
    	,@EndYear = MAX(trans_year)
    FROM #Trans;
    
    DECLARE @Columns NVARCHAR(max)
    	,@Year INT;
    
    SET @Columns = '';
    SET @Year = @StartYear
    
    WHILE @Year <= @EndYear
    BEGIN
    	SET @Columns = @Columns + N',
    	 SUM(CASE WHEN trans_year = ' + CAST(@year AS NVARCHAR(max)) + ' THEN Amount END) AS ' + quotename(cast(@year AS NVARCHAR(max))) + CASE 
    			WHEN @Year > @StartYear
    				THEN N',
    	 SUM(CASE WHEN trans_year <= ' + CAST(@year AS NVARCHAR(max)) + ' THEN Amount END) AS ' + quotename('Total up to ' + cast(@year AS NVARCHAR(max)))
    			ELSE ''
    			END;
    	SET @Year = @Year + 1;
    END
    
    SET @Columns = STUFF(@Columns, 1, 2, '');
    
    DECLARE @SQL NVARCHAR(max);
    
    SET @SQL = ';WITH CTE AS (SELECT trans_year, customer_name, Amount FROM #Trans
    
    WHERE trans_year  >= @StartYear and trans_year <= @EndYear)  
    
    SELECT Customer_Name, ' + @Columns + '
     FROM cte GROUP BY Customer_Name'
    
    PRINT @SQL;
    
    EXECUTE sp_ExecuteSQL @SQL
    	,N'@StartYear INT, @EndYear INT'
    	,@StartYear
    	,@EndYear
    
    DROP TABLE #Trans;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by SnowShine429 Monday, January 27, 2014 10:56 PM
    Monday, January 20, 2014 6:33 PM
    Moderator
  • I added your case to that article (see after UPDATE in bold)

    http://social.technet.microsoft.com/wiki/contents/articles/21135.t-sql-create-report-for-last-10-years-of-data.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 20, 2014 6:41 PM
    Moderator