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?