My Output is correct but I want to know how actually STUFF works.
I have the simple query which returns me total number of months between
SELECT DISTINCT ','
+ Quotename(CONVERT(CHAR(10), startdate, 120))
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@startdate datetime = '1-Jan-2014',
@enddate datetime = '1-Jun-2014'
;with cte (StartDate, EndDate) as
select min(@startdate) StartDate, max(@enddate) EndDate
select dateadd(mm, 1, StartDate), EndDate
where StartDate < EndDate
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), StartDate, 120))
FOR XML PATH(''), TYPE
drop table #tempDates
Stuff works on strings, the only thing its doing on your SQL is remove the initial comma from position 1. Without stuff it would look like ,a,b,c,d but when you stuff the position one with an empty value it transforms it to a,b,c,d
Your question is probably more about what FOR XML is doing. In this case, the
FOR XML is being applied as a "trick" to concatenate all the rows from
#tempDates in one long comma-separated string ,a,b,c,d and stuff is just removing that first comma.
For xml is creating a string x = ',a' + ',b' +',c' + ',d' so x ends up as ',a,b,c,d'
Stuff(x,1,1,'') is replacing only the comma in position 1 to 1 with '' therefore is now x='a,b,c,d'
[STUFF ( character_expression , start , length , replaceWith_expression )]