change a result into one record

Question

change a result into one record

dmarz96

Old Hand

Points: 305
More actions
February 26, 2013 at 12:49 pm

#269132

this is the code I used to retrieve a record set.
SELECT
p.HPROP,
p.STYPE,
p.SVALUE,
NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END,
SecDepIntRate = CASE WHEN p.STYPE = 'DDEPOSITINTEREST' THEN p.hvalue END
FROM PROPOPTIONS P
WHERE p.hprop = '240' and p.STYPE IN ('nsffee','DDEPOSITINTEREST')
my result is as followed
HPROPSTYPE SVALUENSFFeeSecDepIntRate
240DDEPOSITINTEREST0NULL3000.0000
240NSFFee 50.0000NULL
is there a way to get only one record or am i doint this wrong.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

You could use SELECT TOP 1, but I don't know if that's what you really want.

If you have more than one row meeting your search criteria, you should either want to return both or further refine your WHERE clause to return only the specific one you want. If you do want only one of them (say the last one added) and use TOP, make sure you include an ORDER BY clause to make the results consistent.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

dmarz96 Old Hand Points: 305 More actions · Answer 2

dmarz96

Old Hand

Points: 305

February 26, 2013 at 1:25 pm

#1591304

That is not going to work for me.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

dmarz96 (2/26/2013)
That is not going to work for me.

So, what is it that you actually want? which of the two row do you want returned or do you want to somehow combine the rows? Be specific.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dmarz96 Old Hand Points: 305 More actions · Answer 4

The ladder I want to combine the rows into one record.

the code

SELECT

p.HPROP,

p.STYPE,

p.SVALUE,

NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END,

SecDepIntRate = CASE WHEN p.STYPE = 'DDEPOSITINTEREST' THEN p.hvalue END

FROM PROPOPTIONS P

WHERE p.hprop = '240' and p.STYPE IN ('nsffee','DDEPOSITINTEREST')

the result

HPROPSTYPE SVALUENSFFeeSecDepIntRate

240DDEPOSITINTEREST0NULL3000.0000

240NSFFee 50.0000NULL

what I'm looking for is

hprop nsffee SecDepIntRate

240 50.0000 3000.0000

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Like this?

WITH PriorResults (HPROP, STYPE, SVALUE, NSFFee, SecDepIntRate)

AS (

SELECT 240, 'DDEPOSITINTEREST', 0, NULL, 3000.0000

UNION ALL SELECT 240, 'NSFFee', 0, 50.0000, NULL

)

SELECT HPROP

,NSFFee=SUM(NSFFee)

,SecDepIntRate=SUM(SecDepIntRate)

FROM PriorResults

GROUP BY HPROP

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

dwain.c (2/26/2013)
Like this?

Looks like it. Heh... we make a hell of a tag team. I'll flush out the requiremments, you write the code. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Jeff Moden (2/26/2013)
dwain.c (2/26/2013)
Like this?
Looks like it. Heh... we make a hell of a tag team. I'll flush out the requiremments, you write the code. 🙂

Yeah, aren't we though?

I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in that row but 5 in the first row.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

dwain.c (2/26/2013)
I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in that row but 5 in the first row.

Agreed.

@dmarz96,

Just to give you a leg up for your future posts, take a look at the article at the first link in my signature line below. It'll really help you get a very high quality coded answer in a pretty short time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (2/26/2013)
dwain.c (2/26/2013)
I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in that row but 5 in the first row.
Agreed.
@dmarz96,
Just to give you a leg up for your future posts, take a look at the article at the first link in my signature line below. It'll really help you get a very high quality coded answer in a pretty short time.

Jeff, you and Dwain may have missed the preceeding part of this case.

@dmarz96, it's much easier to see what you are trying to do, now that I've seen both threads. Often, breaking a case up into different threads like this causes confusion and fragmented replies as no single thread has all of the information required to put together a solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

, x.FAC_ACTIVITY [FAC_ACTIVITY]

, x.NUMMAINTEMP [NUMMAINTEMP]

, LH.dtLeaseFrom [LHdtLeaseFrom]

, LH.dtLeaseTo [LHdtleaseto]

, crp.destimated [crpdestimated]

--, pps.HValue [ppsNSFFEE]

,pps.NSFFee

,pps.MTMFee

,pps.SecDepIntRate

, m.stext [memoStext]

FROM vw_DK_RMMISCDATA x

LEFT JOIN Property p

ON x.RM_ID = p.sCode

OUTER APPLY ( -- flatten relevant rows from PropOptions

SELECT

HPROP= MAX(po.HPROP),

STYPE= MAX(po.STYPE),

SVALUE= MAX(po.SVALUE),

NSFFee= MAX(CASE WHEN po.STYPE = 'nsffee' THEN po.hvalue END),

MTMFee= MAX(CASE WHEN po.STYPE = 'MTMFee' THEN po.hvalue END),

SecDepIntRate = MAX(CASE WHEN po.STYPE = 'DDEPOSITINTEREST' THEN po.hvalue END)

FROM PROPOPTIONS po

WHERE po.HPROP = p.hmy -- outer reference

AND po.STYPE IN ('nsffee', 'MTMFee', 'DDEPOSITINTEREST') -- filter

) pps

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86948 More actions · Answer 11

ChrisM@Work (2/27/2013)
Jeff, you and Dwain may have missed the preceeding part of this case.
@dmarz96, it's much easier to see what you are trying to do, now that I've seen both threads. Often, breaking a case up into different threads like this causes confusion and fragmented replies as no single thread has all of the information required to put together a solution.

Indeed I did but it looks like you've got it covered.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

dmarz96 (2/26/2013)is there a way to get only one record or am i doint this wrong.

I think this is the statement that caused me to think only one of the rows was required.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 13

Thank you, Jeff and Dwain, for following up.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions