Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » change a result into one record
Add to briefcase
14 posts, Page 1 of 2
12»»

change a result into one record Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
dmarz96
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: Tuesday, February 26, 2013 1:23 PM
Points: 8, Visits: 10
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
Ed Wagner
Posted Tuesday, February 26, 2013 1:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,856, Visits: 528
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.
Post #1424231
dmarz96
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: Tuesday, February 26, 2013 1:23 PM
Points: 8, Visits: 10
That is not going to work for me.
Post #1424233
Jeff Moden
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: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1424238
dmarz96
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: Tuesday, February 26, 2013 1:23 PM
Points: 8, Visits: 10
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
dwain.c
Posted Tuesday, February 26, 2013 6:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 2,340, Visits: 3,165
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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1424301
Jeff Moden
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: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1424309
dwain.c
Posted Tuesday, February 26, 2013 8:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 2,340, Visits: 3,165
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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1424310
Jeff Moden
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: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1424312
ChrisM@Work
Posted Wednesday, February 27, 2013 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 5,602, Visits: 10,953
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
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.