Solved

SQL Coalesce

Posted on 2014-07-22
8
218 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 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