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

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!
dunkin1969Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You don't need a loop and shouldn't use one.


SELECT x, x*2+1 AS y
FROM MyTable
0
 
lluddenConnect With a Mentor Commented:
Why do a loop?

SELECT X, (2*[X])+1 AS Y FROM MyTable
0
 
dunkin1969Author Commented:
Thanks guys!  I couldn't see the forest through the trees!!!
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Dale FyeCommented:
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
 
dunkin1969Author Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
dunkin1969Author Commented:
many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.