Actually, you need to look at a little bit more of it:

SELECT p1.id ,CustomerName , OrderDate,

STUFF ( ( SELECT ','+ ProductName

FROM CTE p2

WHERE p2.id = p1.id

ORDER BY ID

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values

Part 1: SELECT statement

Part 2: STUFF function returning a field.

Part 3: Subquery using FOR XML PATH(''),TYPE

Going from back to front:

Part 3: This correlated subquery builds a comma-delimited list of ProductNames for the id in the parent query.

: FOR XML PATH(''),TYPE says to build that query as an XML datatype, with no node names.

: the .value('.', 'VARCHAR(MAX)') says to return that XML as a varchar(max) field. This allows you to return data that would otherwise be converted to XML tags (i.e. an "&" would be replaced with "& amp;", a "<" would be replaced with "& lt;", etc.) as a string. (Technically, this would be part 4, but it's easier to explain it here.

Part 2: Stuff function starts at the first character (leading comma), and replaces one character with an empty string (in effect, removes the leading space from the comma-delimited string). Returns this string as the column Concat_Values.

Part 1: Return all of this data grouped by appropriate columns.

Edit: put a space after the "&" so it wouldn't show as the symbol - just realize that the space isn't there.