Solved

SQL Coalesce

Posted on 2014-07-22
8
223 Views
Last Modified: 2014-07-23
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...
0
Comment
Question by:quest_capital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 8

Expert Comment

by:5teveo
ID: 40211768
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
0
 

Author Comment

by:quest_capital
ID: 40211812
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.
0
 
LVL 8

Expert Comment

by:5teveo
ID: 40211881
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
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

by:quest_capital
ID: 40211971
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
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40214095
To collapse multiple rows into a single row is not what coalesce() is designed for

FOR XML PATH is what you are seeking
Use that with STUFF() to push the pieces into a delimited string (and remove the unwanted first delimiter)

Like this:
SELECT
      o.intOrderID
    , o.name
    , o.company
    , ca1.notes
    , o.bonus
FROM [Order] o
      CROSS APPLY (
                  SELECT
                        STUFF((
                              SELECT
                                    ', ' + p.[strPaymentMethod] + ':$' + CAST(p.[decPaymentAmount] AS varchar(20))
                              FROM [GCM_Payment] AS p
                              WHERE p.intOrderID = o.intOrderID
                              FOR XML PATH ('')
                        )
                        , 1, 1, '')
            ) ca1 (Notes)
;

| INTORDERID |      NAME |  COMPANY |                    NOTES | BONUS |
|------------|-----------|----------|--------------------------|-------|
|          1 | namo namo | companie |  Visa:$1000, Check:$1000 |   111 |		


CREATE TABLE [Order]
	([intOrderID] int, [name] varchar(9), [company] varchar(8), [bonus] numeric)
;
	
INSERT INTO [Order]
	([intOrderID], [name], [company], [bonus])
VALUES
	(1, 'namo namo', 'companie', 111.11)
;

CREATE TABLE  GCM_Payment
	([strPaymentMethod] varchar(5), [decPaymentAmount] int, [intOrderID] int)
;
	
INSERT INTO  GCM_Payment
	([strPaymentMethod], [decPaymentAmount], [intOrderID])
VALUES
	('Visa', 1000.00, 1),
	('Check', 1000.00, 1)
;

http://sqlfiddle.com/#!3/e7e65/2

Open in new window

{+ edit}
By the way I hope you don't have a table named Order
it will forever cause issues with this common item of SQL:
ORDER BY
0
 

Author Comment

by:quest_capital
ID: 40214161
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
0
 

Author Closing Comment

by:quest_capital
ID: 40214166
Great illustration
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40214181
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)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question