1

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;

1 Answer 1

2

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)
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.