asked on

SQL Coalesce

The following statement work on it's own:

declare @res varchar(8000)
select @res = coalesce(@res + ', ', '') + [strPaymentMethod] + ':$' + cast([decPaymentAmount] as varchar(20))
from [GCM_Payment] where intOrderID = 6677 
select @res as pay

Open in new window

But how do I integrate it into anther query like

declare @res varchar(8000)
select 
intOrderID 
,name
,company
,(select @res = coalesce(@res + ', ', '') + [strPaymentMethod] + ':$' + cast([decPaymentAmount] as varchar(20))
from [GCM_Payment] where intOrderID = o.intOrderID select @res as pay)
,bonus
from Order o

Open in new window

This will not work I get all type of syntax errors. Please correct my query...

5teveo

declare @res varchar(8000) <-- not needed

Try... Pay will be column name of SQL expression

select
intOrderID
,name
,company
,(coalesce(@res + ', ', '') + [strPaymentMethod] + ':$' + cast([decPaymentAmount] as varchar(20))
from [GCM_Payment] where intOrderID = o.intOrderID) as Pay
,bonus
from Order o

quest_capital

ASKER

5teveo

I get this error:
Incorrect syntax near the keyword 'from'.

Don't you need a select with a from?
Also It seem that I still would need @res because it's still being used in the statement.

5teveo

oops! I was too quick...

something like this... Im not running on my sql server just putting together on the fly...

@res not needed inside query... It was just holding results string on your query...

select
intOrderID
,name
,company
,(Select [strPaymentMethod] + ':$' + cast([decPaymentAmount] as varchar(20))
from [GCM_Payment] where intOrderID = o.intOrderID) as Pay
,bonus
from Order o

quest_capital

ASKER

5teveo

The problem is that:
,(Select [strPaymentMethod] + ':$' + cast([decPaymentAmount] as varchar(20))
from [GCM_Payment] where intOrderID = o.intOrderID) as Pay

will give me two rows (giving me an error), like..
Visa:$1000.00
Check:$1000.00

the coalesce is suppose to give me one field one row like
Visa:$1000.00, Check:$1000.00

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

quest_capital

ASKER

PortletPaul

Thanks for the through explanation that was great and for that you get the points.
However, I already new about that, I'm just dealing with the dark ages of SQL 2000, so that option is not available.
I had to come up the anther option.

Thx

quest_capital

ASKER

Great illustration

PortletPaul

So you do have a method?
Thanks for the point but I feel guilty now.

for next time: perhaps use a tag, or a note in the question, to indicate SQL 2000 (I haven't seen a question on that version for quite some time and I forget its (many) limitations)