sql-server-2008left-joindeclarederived-table

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


I'm trying to create a comma separated list and I'm using a derived table. But I cannot declare the variable within the LEFT OUTER JOIN... how can I do this?

    LEFT OUTER JOIN (

               DECLARE @String AS VARCHAR(MAX) = NULL
               SELECT @String = COALESCE(@String + ', ','') + Name
                 FROM MyTable
               SELECT @String, Col1
                 FROM MyTable
                GROUP BY Col1

    ) AS T8
   ON This = That

It gives me an error on the Declare keyword that says, Incorrect Syntax.

Thanks!


Solution

  • 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