SQLTeam.com | Weblogs | Forums

How to interpret values of a column which in turn contains name other columns?

sql2012

#1

Hi Experts,

I have a table with following columns and data. Formula might be combination of any columns & calculations. Like Rate x 0.5/100 etc

Rate Qty TotFormula

10 5 Rate x Qty
10 5 Rate / Qty
10 5 Rate - Qty
My output should be something like this:

Rate Qty Result
10 5 50
10 5 2
10 5 5
Any idea how to achieve this using TSQL Query?

Query for creating sample data:

create table test
(
Rate int,
Qty int,
TotFormula varchar(50)
);
insert into test values(10,5,'Rate x Qty');
insert into test values(10,5,'Rate / Qty');
insert into test values(10,5,'Rate - Qty');

Thanks & Regards,
Naveen J V


#2

hi

I have tried with a small variation in the table:

create table test
(
Rate int,
Qty int,
test varchar(2),
TotFormula varchar(50)
);
insert into test values(10,5,'x',NULL);
insert into test values(10,5,'/',NULL);
insert into test values(10,5,'-',NULL);

select * from test

update test
set TotFormula =
(case test
when 'x' then rate*Qty
when '/' then rate/Qty
when '-' then rate-Qty
End )


#3

Thanks for you reply.
We cannot do this manually. Formula is given by the end user using the other columns available. Formula is not fixed. It can be anything like Rate * 0.1 or Rate * Qty /100


#4

This may help: