Solved

SQL Coalesce

Posted on 2014-07-22
8
214 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 48

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 48

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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