Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to Loop Through a Table in SQL and apply a Calculation to each record

Posted on 2014-02-17
7
Medium Priority
?
727 Views
Last Modified: 2014-02-17
Hi Experts,

I have MyTable with Column1 (X) containing test scores 1 through 100 (1,2,3,...100)

I want each row to be used to calculate (Y) in the formula:  Y = 2X+1

So my results will look like this:

X     Y
1     3
2     5
3     7
4     9
5     11
etc
etc

How would I write a loop for this?

Thanks you!
0
Comment
Question by:dunkin1969
[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
7 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1200 total points
ID: 39865586
You don't need a loop and shouldn't use one.


SELECT x, x*2+1 AS y
FROM MyTable
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 800 total points
ID: 39865590
Why do a loop?

SELECT X, (2*[X])+1 AS Y FROM MyTable
0
 

Author Comment

by:dunkin1969
ID: 39865602
Thanks guys!  I couldn't see the forest through the trees!!!
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39865603
Why would you want to store this number, when you can compute it in a query at any time?  Generally, storing computed values is a bad idea.  Not always, but generally.
0
 

Author Comment

by:dunkin1969
ID: 39865611
oh by the way, let's say I don't have a table... and I want the same results using X = 1 through 100.  How would i do that?  Some type of counter?

Let me know if this should be posted as a new topic.  THANK YOU !!!
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1200 total points
ID: 39865659
Most efficient is a cross join to generate the needed numbers.  Code below gens numbers 0 thru 99, so I just add 1 to the gen'd values to get 1 thru 100.


;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
)
SELECT (t.tally + 1) AS x, (t.tally*2)+1 AS y
FROM cteTally t
ORDER BY 1
0
 

Author Comment

by:dunkin1969
ID: 39865728
many thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

597 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