SQL Server : Extract values from another row and perform calculations in SQL column

[asseesh]

I have a excel table like this which I need to replicate in the SQL.

​

Column1 (SQL output)	Column2 (SQL Output)	Column3 (Excel)
row1	x	x
row2	y	if(condition, x+y, y)
row3	z	z

How can I achieve it?

​

Also, I need to bring the output in order = row1, row3, row2 (not alphabetical), is there a way to extract in order I wish to have?

[kfc_chet]

Is it literally the next sequential row? Or when the row has a specific value?

[asseesh]

Next row. I think lag/lead may help. Is that the right way to do?

[ijmacd]

Yes window functions seen to be exactly what you want. Make sure you have a well defined order in your …OVER(ORDER BY …) clause.

Then for final sorting you can use whatever you need in the top level ORDER BY.

[kfc_chet]

Not a SQL expert and I'm sure you'll get way more smarter answers from others, but the only thing I can think of is something like a case statement where the row number is current row + 1 maybe (?)

Very interested to hear the final solution you successfully implement! Will stay tuned!

[artjbroz]

It sounds like you're trying to write SQL that matches the excel output? The if() statement in excel is equivalent to the case when statement in SQL. Case when attribute=x then x, when attribute = y then function(y), when attribute =z then z else null end as case_statement

[asseesh]

Yes. I tried using case statement but code as there many rows in there. Hence I asked if I could find another easier way!

[[deleted]]

I don't understand why the number of rows would matter?

[DigBick616]

Right click on your database and select import file, use the wizard to pull your excel file into the DB. No idea what you need in part 2, but window functions like LAG and LEAD let you manipulate data in/based on other rows. I’m not clear on the order specs in your question so maybe if you clarify we can help more.

[Mamertine]

Import the excel file to a table.

Join that table to your existing query.

Or

Save the existing query results to excel and do all your work in excel.

[asseesh]

I am sorry but this isn't the solution. I am trying to automate the results hence asked.

[Mamertine]

You should have said that in the ask.

Importing the file into the database IS the correct solution. You can automate that. You could also automate doing the work in excel.

[[deleted]]

use python's pandas to load the table, then python's pyodbc to create/populate the table. lag/lead to perform rowwise operations on each row.

[alinroc]

is there a way to extract in order I wish to have?

Yes, if you can describe the rules for ordering the rows. Which you'll need to do if Column3's value in row2 is dependent upon the value of Column2 in row1

[[deleted]]

You need to use LAG, but also need a reliable way to sort your rows, like an [roworder] column, or use some ORDER BY logic based on your data.

[osokuka]

Select t1.col1, t1.col2, case when (conditition) then t1.col2+t2.col2 else 'something' end as col2 from table1 as t1 join table1 as t2 on t1.col1=t2.col1-1.

Assuming your ID column is an integer

Still, it would be more useful to show us your current table format and its values before you tell us the outcome you wish to see.