What is the meaning of SELECT ... FOR XML PATH(' '),1,1)?

Question

There's no real technique to learn here. It's just a cute trick to concatenate multiple rows of data into a single string. It's more a quirky use of a feature than an intended use of the XML formatting feature.

SELECT ',' + ColumnName ... FOR XML PATH('')

generates a set of comma separated values, based on combining multiple rows of data from the ColumnName column. It will produce a value like ,abc,def,ghi,jkl.

STUFF(...,1,1,'')

Is then used to remove the leading comma that the previous trick generated, see STUFF for details about its parameters.

(Strangely, a lot of people tend to refer to this method of generating a comma separated set of values as "the STUFF method" despite the STUFF only being responsible for a final bit of trimming)

SQL you were referencing is used for string concatenation in MSSQL.

It concatenates rows by prepending , using for xml path to result ,a,b,c,d. Then using stuff it replaces first , for , thus removing it.

The ('') in for xml path is used to remove wrapper node, that is being automatically created. Otherwise it would look like <row>,a,b,c,d</row>.

...
stuff(
  (
  select ',' + CAST(t2.Value as varchar(10)) from #t t2 where t1.id = t2.id 
  for xml path('')
  )
,1,1,'') as Value
...

• more on stuff
• more on for xml path