SQL: How can I declare a variable inside a derived table?

Solution 1:

You cannot declare a variable inside a derived table.

But you may declare it outside of the statement and use it in the same way as you did in your example

Solution 2:

Your requirement doesn't make sense because the variable can't really be used inside the table variable. And if you want to use it after the table variable, it still doesn't make sense... do you expect multiple instances of the variable, once for each distinct value of Col1? Maybe you meant this:

LEFT OUTER JOIN
(
  SELECT Col1, String = STUFF((
     SELECT ',' + Name
        FROM dbo.MyTable AS i 
        WHERE i.Col1 = o.Col1 
        FOR XML PATH(''), 
        TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,'')
  FROM dbo.MyTable AS o
  GROUP BY Col1
) AS T8
ON This = That

However, T8 kind of scares me a little. How many tables are already involved in this join?