Solved

SQL Coalesce

Posted on 2014-07-22
8
193 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Great illustration
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

12 Experts available now in Live!

Get 1:1 Help Now