Solved

SQL Coalesce

Posted on 2014-07-22
8
201 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Title # Comments Views Activity
Permissions on Database 11 37
Azure SQL Insert not working suddenly 11 21
Sql query 107 25
ORA-00923: FROM keyword not found where expected 3 24
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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now