Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

change a result into one record Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:18 AM
Points: 8, Visits: 13
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
HPROP STYPE SVALUE NSFFee SecDepIntRate
240 DDEPOSITINTEREST 0 NULL 3000.0000
240 NSFFee 50.0000 NULL

is there a way to get only one record or am i doint this wrong.
Post #1424223
Posted Tuesday, February 26, 2013 1:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 4,053, Visits: 3,490
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
Post #1424231
Posted Tuesday, February 26, 2013 1:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:18 AM
Points: 8, Visits: 13
That is not going to work for me.
Post #1424233
Posted Tuesday, February 26, 2013 1:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424238
Posted Tuesday, February 26, 2013 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:18 AM
Points: 8, Visits: 13
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
HPROP STYPE SVALUE NSFFee SecDepIntRate
240 DDEPOSITINTEREST 0 NULL 3000.0000
240 NSFFee 50.0000 NULL



what I'm looking for is
hprop nsffee SecDepIntRate
240 50.0000 3000.0000

Post #1424240
Posted Tuesday, February 26, 2013 6:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 3,420, Visits: 5,348
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1424301
Posted Tuesday, February 26, 2013 8:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424309
Posted Tuesday, February 26, 2013 8:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 3,420, Visits: 5,348
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1424310
Posted Tuesday, February 26, 2013 8:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424312
Posted Wednesday, February 27, 2013 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1424381
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse