Struggling with t-SQL PIVOT

Topic

New Reply

Following code works perfectly with my SQL Server 2008R2 data base:

Code:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 
Name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][2012] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [2012][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [2013] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [2013][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [2014] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [2014][/SIZE][SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] C[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]C[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]D[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DivYear[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]D[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Amount [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Stocks[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Companies [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] C[/SIZE][SIZE=2]    
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Stocks[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DivHistory [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] D [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] C[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]D[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] IJ [/SIZE][SIZE=2]    
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]PIVOT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]SUM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]IJ[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Amount[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FOR[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] IJ[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DivYear [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]IN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][[/SIZE][SIZE=2]2012][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][2013][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][2014][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] P[/SIZE][SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] P[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Name[/SIZE]

As some of the Amounts can be NULL, I modified the code to:

Code:

SELECT [SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 
[2012] as [2012],
[/SIZE][SIZE=2][2013] as [2013],
[/SIZE][SIZE=2][2014] [SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][SIZE=2] [2014][/SIZE]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] C[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]C[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]D[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DivYear[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]D[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Amount [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Stocks[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Companies [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] C[/SIZE]
[SIZE=2][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Stocks[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DivHistory [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] D [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] C[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]D[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Code[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] IJ     [/SIZE]
[SIZE=2][SIZE=2][COLOR=#808080]PIVOT [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]SUM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]ISNULL[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]IJ[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Amount[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]0[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FOR[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] IJ[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DivYear [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]IN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][[/SIZE][SIZE=2]2012][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][2013][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][2014][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] P[/SIZE]
[SIZE=2][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] P[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Name[/SIZE]
[/SIZE][/SIZE]

Which resulted in the ‘at least to me’ incomprehensible error:
Msg 102, Level 15, State 1, Line 13
[/COLOR]Incorrect syntax near ‘(‘.

Where went it wrong?
[/SIZE]
[/SIZE]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))

[/SIZE]

[/COLOR][/SIZE]

Reply | Quote

Replies

Have you tried using the ISNULL on the D.Amount in the Subquery?

Reply | Quote

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:confused:

Reply | Quote

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

Reply | Quote