create table source1
(car int,cycle int)
insert into source1
values(150,250)
create table conversation_table
(min int ,max int, speed varchar(30), rating varchar(30))
insert into conversation_table
values(100,200,'Higher','good'),
(200,400,'Highest','better')
;with mycte (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from source1 FOR JSON AUTO ))
)
SELECT unpvt.[Key] as Property, ct.max as Value, ct.speed,ct.rating
FROM mycte AS src
CROSS APPLY OpenJson(src.TheValue) AS unpvt
Join conversation_table ct on unpvt.Value between ct.min and ct.max
drop table source1,conversation_table
case condition in min max values
Hi Expert,
I wanted to update the target table based on the conversation table . if the value is between 100 and 200 then
if the value is between 200 and 400 then
create table source1
(car int,cycle int)
insert into source1
values(150,250)
create table conversation_table
(min int ,max int, speed char, rating char)
insert into conversation_table
values(100,200,'Higher','good'),
(200,400,'Highest','better')
create table target1
(property char, value int, speed char, rating char)
expected output
6 answers
Sort by: Most helpful
-
Jingyang Li 5,891 Reputation points
2022-07-14T19:44:43.93+00:00 -
Shambhu Rai 1,406 Reputation points
2022-07-14T20:36:40.19+00:00 Any other suggestion with simply case condition or easiest one
-
Jingyang Li 5,891 Reputation points
2022-07-14T20:46:18.567+00:00 create table source1 (car int,cycle int) insert into source1 values(150,250) create table conversation_table (min int ,max int, speed varchar(30), rating varchar(30)) insert into conversation_table values(100,200,'Higher','good'), (200,400,'Highest','better') Select Property, ct.max as Value, ct.speed,ct.rating from source1 Unpivot ( val for Property in (Car,cycle) ) as unpvt Join conversation_table ct on unpvt.val between ct.min and ct.max drop table source1,conversation_table
-
Isabellaz-1451 3,616 Reputation points
2022-07-15T02:07:46.163+00:00 Hi @Shambhu Rai
How about change the source1 table like below
create table source1 (Property varchar(20),value int) insert into source1 values('cycle',150),('car',250) create table conversation_table (min int ,max int, speed varchar(30), rating varchar(30)) insert into conversation_table values(100,200,'Higher','good'), (200,400,'Highest','better') select * from source1 cross apply( select top 1* from conversation_table where source1.value >=conversation_table.min and source1.value < conversation_table.max) p
result is like this:
-
LiHong-MSFT 10,046 Reputation points
2022-07-15T02:39:33.95+00:00 Hi @Shambhu Rai
Please check this query:;WITH CTE AS ( SELECT Property,[Value] FROM source1 UNPIVOT([Value] FOR Property IN ([car],[cycle]))U ) SELECT A.Property,B.max AS [Value],B.speed,B.rating FROM CTE A JOIN conversation_table B ON A.Value BETWEEN B.min AND B.max
Best regards,
LiHong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.