-1

i have data with fields as shown below

id grade grade_id year Diff
101 5 7 2022 9
105 k 2 2021 2
106 4 6 2020 5
110 pk 1 2022 1

i want to insert records for same id until we reaches grade = pk , Like shown below for every record in the table .

id grade grade_id year Diff
101 5 7 2022 9
101 4 6 2021 8
101 3 5 2020 7
101 2 4 2019 6
101 1 3 2018 5
101 k 2 2017 4
101 pk 1 2016 3

need help in sql code

Sri Harsha
  • 11
  • 4

1 Answers1

0
create table amish.cte_test
(id int, 
grade int,
year int,
diff int)

insert into amish.cte_test
values (101,5,2022,9)

with recursive temp1( id, grade, year, diff) as
(select id, grade  , year , diff  from amish.cte_test
union all
select id, grade-1, year-1,diff-1 from temp1
where grade-1 > -2) 
select * from temp1
Amish Shah
  • 106
  • 2
  • Thanks Amish, i have 100 of records like given in the input table . how can i run for all the records in the table to get the output .. Please help me – Sri Harsha Sep 16 '22 at 04:10
  • in our input table , grade is defined as varying char .. but u have taken int .. so we r getting conversion error . Could you plz help us – Sri Harsha Sep 16 '22 at 04:22
  • in recursive cte you have to join cte table with main table with id , for your 100 ids. also grade needs to be int or convert it to int as we are reducing its value. Provide sample data and table structure ifyou still need help. – Amish Shah Sep 16 '22 at 17:00
  • sir amish, i have edit the input and output table now .. please help me .. i was able to get records when the grade is int but in my db system grade is varying char . – Sri Harsha Sep 16 '22 at 18:02