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;
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)
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加