Solved

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

Posted on 2014-02-17
7
674 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
7 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 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 200 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 300 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 30
SQL Server 2012 r2 - Make Temp Table Query Faster 5 41
sql server computed columns 11 31
Inserting oldest record into new table. 5 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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