Coalesce / Comma Delimitted List

[quizwedge]

I'm trying to return multiple column, one of which is a column + a comma delimitted list of values. Below is a simplified, non working, query:

(assume @MyBit, @MyVarChar, and @MyValue are all declared)

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @MyBit = 1 THEN ' My Test: ' + (select @MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue) ELSE '' END AS Column2,
FROM TableMain

I get an ADO error: Incorrect syntax near '='

Thoughts?

Thanks

[scripto]

(select @MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue)

use "[ ]" brackets instead of "( )" around the above select statment.

(and remove the last comma before the FROM stmnt)

[quizwedge]

I now have:

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @MyBit = 1 THEN ' My Test: ' + [select @MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue] ELSE '' END AS Column2
FROM TableMain

and I get:
ADO error: Invalid column name 'select @MyVarChar = COALESCE(Column3 + ',', '') from TableSub where Value = @MyValue'

Thanks

[scripto]

my apologies - here is the correct query

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @MyBit = 1 THEN ' My Test: ' + (select COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue) ELSE '' END AS Column2
FROM TableMain

put the "( )" back on and remove the @MyVarChar = (that is where the err is)
you just need select coalesce...