Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Struggling with t-SQL PIVOT

    Following code works perfectly with my SQL Server 2008R2 data base:
    Code:
    SELECT Code, 
    Name,
    [2012] as [2012],
     [2013] as [2013],
     [2014] as [2014] 
    FROM (SELECT C.Code,C.Name,D.DivYear,D.Amount FROM Stocks.Companies AS C    
    INNER JOIN Stocks.DivHistory as D ON C.Code=D.Code)as IJ     
    PIVOT (SUM(IJ.Amount)            
    FOR IJ.DivYear IN([2012],[2013],[2014]))as P 
    ORDER BY P.Name
    As some of the Amounts can be NULL, I modified the code to:
    Code:
    SELECT Code,
    Name, 
    [2012] as [2012],
    [2013] as [2013],
    [2014] as [2014]
    FROM (SELECT C.Code,C.Name,D.DivYear,D.Amount FROM Stocks.Companies AS C
    INNER JOIN Stocks.DivHistory as D ON C.Code=D.Code)as IJ     
    PIVOT (SUM(ISNULL(IJ.Amount,0))
    FOR IJ.DivYear IN([2012],[2013],[2014]))as P
    ORDER BY P.Name
    
    Which resulted in the 'at least to me' incomprehensible error:
    Msg 102, Level 15, State 1, Line 13
    Incorrect syntax near '('.

    Where went it wrong?

    A secondary question is: Can the IN close be made more generic? Something like
    ...FOR IJ.DivYear IN (SELECT TOP 3 YEAR(DDate) AS DY FROM Stocks.DivHistory ORDER BY DY Desc))













  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Have you tried using the ISNULL on the D.Amount in the Subquery?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Yes, I did and got the same error.

    Following code did circumvent the problem:
    Code:
    SELECT Code, 
    Name,
    ISNULL([2012],0) as [2012],
     ISNULL([2013],0) as [2013],
     ISNULL([2014],0) as [2014] 
    FROM (SELECT C.Code,C.Name,D.DivYear,D.Amount FROM Stocks.Companies AS C    
    INNER JOIN Stocks.DivHistory as D ON C.Code=D.Code)as IJ     
    PIVOT (SUM(IJ.Amount)            
    FOR IJ.DivYear IN([2012],[2013],[2014]))as P 
    ORDER BY P.Name
    But I find this rather clumsy. I still have no clue as to where the Original code went wrong

  4. #4
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    The more generic question can be solved by using dynamic SQL. Interpreting some googled results, I put the following reminder to myself together:

    Generic Approach

    1. Define an SQL variable that will hold the plain text of the Pivot query (e.g. @strPvt)
    2. Within that text, replace the list with the unknown boundaries by another SQL variable (e.g. @strYears)
    3. Construct the @strYears variable by concatenating the results of a ‘SELECT DISTINCT …’ query
    4. Execute the Pivot query by means of the sp_executesql system procedure

    Step by step Solution

    1. Define the SQL variable that holds the Pivot SQL statement and replace the list definitions with new SQL variables. We have in this particular case two slightly different lists as we want the NULLs in the final result to be replaced by true zeroes.
    Code:
    DECLARE @strYearsSelect nvarchar(max)=NULL,
        @strYearsPvt nvarchar(max)=NULL, 
        @strPvt nvarchar(max)=N''
    SET @strPvt = 'SELECT Code, Name,' + @strYearsSelect + '
       FROM (SELECT C.Code,C.Name,D.DivYear,D.Amount FROM Stocks.Companies AS C
       INNER JOIN Stocks.DivHistory as D ON C.Code=D.Code) as IJ 
       PIVOT (SUM(IJ.Amount) FOR IJ.DivYear IN (' + @strYearsPvt + ')) AS P
       ORDER BY P.Name'
    2. Construct @strYearsPvt which will be used in the PIVOT … IN clause. We know already that the exact list will be provided by: SELECT DISTINCT DivYear FROM Stocks.DivHistory
    The challenge is now to transform that result into a list that can be used in the query. The square brackets surrounding the year are generated by the QUOTENAME function. Concatenating the years into a useful list without generating a leading or trailing semi-column is done by using a trick that involves the ISNULL function and the behaviour of NULLs in operations . (An operation on a NULL variable results in a NULL result.)
    Code:
    @strYearsPvt nvarchar(max)=NULL
    @strYearsPvt = ISNULL(@strYearsPvt + N', ',N'') + QUOTENAME(DivYear)
    As @strYearsPvt was NULL by its declaration, the ISNULL function will reduce it to an empty string by the time the first bracketed year is to be concatenated.
    The final statement is now:
    Code:
    SELECT @strYearsPvt = ISNULL(@strYearsPvt + N', ',N'') + QUOTENAME(DivYear)
        FROM (SELECT DISTINCT DivYear FROM Stocks.DivHistory) as Yp
    3. The same approach will be used to generate the second variable @strYearsSelect that will contain an ISNULL function as part of its text. Nothing complicated, but you have to pay a little bit more attention.
    Code:
    SELECT @strYearsSelect = ISNULL(@strYearsSelect + N', ',N'') + 
        'ISNULL(' + QUOTENAME(DivYear) + ',0) AS ' + QUOTENAME(DivYear)
        FROM (SELECT DISTINCT DivYear FROM Stocks.DivHistory) as Ys
    4. And finally we put together the whole procedure and execute it:
    Code:
    DECLARE @strYearsSelect nvarchar(max)=NULL,
        @strYearsPvt nvarchar(max)=NULL, 
        @strPvt nvarchar(max)=N''
        
    SELECT @strYearsSelect = ISNULL(@strYearsSelect + N', ',N'') + 
        'ISNULL(' + QUOTENAME(DivYear) + ',0) AS ' + QUOTENAME(DivYear)
        FROM (SELECT DISTINCT DivYear FROM Stocks.DivHistory) as Ys
    
    SELECT @strYearsPvt = ISNULL(@strYearsPvt + N', ',N'') + QUOTENAME(DivYear)
        FROM (SELECT DISTINCT DivYear FROM Stocks.DivHistory) as Yp
    
    SET @strPvt = 'SELECT Code, Name,' + @strYearsSelect + '
       FROM (SELECT C.Code,C.Name,D.DivYear,D.Amount FROM Stocks.Companies AS C
       INNER JOIN Stocks.DivHistory as D ON C.Code=D.Code) as IJ 
       PIVOT (SUM(IJ.Amount) FOR IJ.DivYear IN (' + @strYearsPvt + ')) AS P
       ORDER BY P.Name'
    
    EXEC sp_executesql @strPvt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •