CODEWITHSUNDEEP
sqlsql-servert-sql
S.Elgar
S.Elgar

Reputation: 25

Dynamic Pivot Function - SQL

I'm having a small issue which I believe is to do with my STUFF Function. The below query gives the below print which has a comma before the initial column after the IN.

QUERY:

DECLARE @Columns    nvarchar(max); 
DECLARE @sql        nvarchar(max); 
SET     @Columns = N'' ; 
SELECT  @Columns += N' , p. '+ QUOTENAME(FiscalWeek) 
    FROM 
        (   SELECT FiscalWeek
            FROM MI.dbo.UtilisationSummary_Weekly  
            WHERE   MaxDate > dateadd(ww,-8, getdate())     ) AS x; 

SET @sql = N' SELECT Fee_Earner, '+ STUFF(@columns, 1,2, '') +' 
                FROM    
                        (   SELECT Fee_Earner, WeeklyUtilisation,  FiscalWeek
                            FROM    MI.dbo.UtilisationSummary_Weekly  
                            WHERE   MaxDate > dateadd(ww, -8, getdate() )) AS SourceTable   
                PIVOT   ( Sum(WeeklyUtilisation) FOR FiscalWeek IN (
                            ' + STUFF(REPLACE(@columns, ' p.[', '['), 1 , 1 ,'') +' ) )
                AS p;'; 
PRINT @sql 
EXEC sp_executesql @sql

PRINTED RESULTS: 

SELECT Fee_Earner,  p. [40] , p. [41] , p. [42] , p. [43] , p. [44] , p. [45] , p. [46] , p. [47] , p. [48] 
                FROM    
                        (   SELECT Fee_Earner, WeeklyUtilisation,  FiscalWeek
                            FROM    MI.dbo.UtilisationSummary_Weekly  
                            WHERE   MaxDate > dateadd(ww, -8, getdate() )) AS SourceTable   
                PIVOT   ( Sum(WeeklyUtilisation) FOR FiscalWeek IN (
                            , p. [40] , p. [41] , p. [42] , p. [43] , p. [44] , p. [45] , p. [46] , p. [47] , p. [48] ) )
                AS p;

Upvotes: 1

Views: 43

Answers (1)

PSK
PSK

Reputation: 17943

You have a space before ,, STUFF is removing space not comma.

Change following line

SELECT  @Columns += N' , p. '+ QUOTENAME(FiscalWeek)

to

SELECT  @Columns += N', p. '+ QUOTENAME(FiscalWeek)

Upvotes: 2

Related Questions