# 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!
###### Who is Participating?

Senior DBACommented:
You don't need a loop and shouldn't use one.

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

Commented:
Why do a loop?

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

Author Commented:
Thanks guys!  I couldn't see the forest through the trees!!!
0

Commented:
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 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

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

Author 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.