469,128 Members | 1,523 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

Coalesce / Comma Delimitted List

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
Oct 19 '06 #1
3 3858
scripto
143 100+
(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)
Oct 19 '06 #2
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
Oct 19 '06 #3
scripto
143 100+
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...
Oct 20 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

27 posts views Thread by Alberto Vera | last post: by
2 posts views Thread by Jeff Roughgarden | last post: by
3 posts views Thread by Steffen Vulpius | last post: by
5 posts views Thread by Simon Windsor | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.