I work on sql server 2017 i have table have dense rank over FeatureString and column store rank increment is technology id
as below
create table #partsfeature
(
PartId int,
FeatureName varchar(300),
FeatureValue varchar(300),
FeatureString varchar(300),
TechnologyId int
)
insert into #partsfeature(PartId,FeatureName,FeatureValue,FeatureString,TechnologyId)
values
(1211,'AC','5V','AC(5V)Boil(10v)Temp(5V)',1),
(1211,'Boil','10v','AC(5V)Boil(10v)Temp(5V)',1),
(1211,'Temp','5V','AC(5V)Boil(10v)Temp(5V)',1),
(2421,'grail','51V','Alc(5V)Coil(9V)grail(51V)',2),
(2421,'Coil','9V','Alc(5V)Coil(9V)grail(51V)',2),
(2421,'Alc','5V','Alc(5V)Coil(9V)grail(51V)',2),
(6211,'compress','33v','compress(33v)heat(90v)push(80v)',3),
(6211,'heat','90v','compress(33v)heat(90v)push(80v)',3),
(6211,'push','80v','compress(33v)heat(90v)push(80v)',3)
Now max technology id on table part feature is 3
I need New Inserted data will be 4,5 for technology id
New Inserted Data as below
(7791,'AC','5V','AC(5V)Boil(10v)Temp(52V)'),
(7791,'Boil','10v','AC(5V)Boil(10v)Temp(52V)'),
(7791,'Temp','52V','AC(5V)Boil(10v)Temp(52V)'),
(8321,'Angit','50V','Angit(50V)Fan(9v)Hot(3V),Wether(12V)'),
(8321,'Fan','9v','Angit(50V)Fan(9v)Hot(3V),Wether(12V)'),
(8321,'Hot','3V','Angit(50V)Fan(9v)Hot(3V),Wether(12V)'),
(8321,'Wether','12V','Angit(50V)Fan(9v)Hot(3V),Wether(12V)')
so how to make select query over Feature String for column Technology Id as 4,5 for New inserted Data
expected result i need is
PartId FeatureName FeatureValue FeatureString TechnologyId
7791 AC 5V AC(5V)Boil(10v)Temp(52V) 4
7791 Boil 10v AC(5V)Boil(10v)Temp(52V) 4
7791 Temp 52V AC(5V)Boil(10v)Temp(52V) 4
8321 Angit 50V Angit(50V)Fan(9v)Hot(3V),Wether(12V) 5
8321 Fan 9v Angit(50V)Fan(9v)Hot(3V),Wether(12V) 5
8321 Hot 3V Angit(50V)Fan(9v)Hot(3V),Wether(12V) 5
8321 Wether 12V Angit(50V)Fan(9v)Hot(3V),Wether(12V) 5
What I have tried:
select *,DENSE_RANK() OVER (
ORDER BY FeatureString
) TechnologyId from #partsfeature