I work on sql server 2017
I have table #partsfeature
already exist as below
create table #partsfeature
(
PartId int,
FeatureName varchar(300),
FeatureValue varchar(300),
TechnologyId int
)
insert into #partsfeature(PartId,FeatureName,FeatureValue,TechnologyId)
values
(1211,'AC','5V',1),
(2421,'grail','51V',2),
(6211,'compress','33v',3)
my issue Done For Part id 3900
it take wrong
Technology Id 7 and Correct Must be 2
Because Feature name and Feature Value Exist
So it Must Take Same TechnologyId
Exist
on Table #partsfeature
as Technology Id 2
.
correct will be as Below
+--------+--------------+---------------+-------------
| PartID | FeatureName | FeatureValue | TechnologyId
+--------+--------------+---------------+-------------
| 3900 | grail | 51V | 2
+--------+--------------+---------------+-------
what I try is
insert into #partsfeature(PartId,FeatureName,FeatureValue,TechnologyId)
select PartId,FeatureName,FeatureValue,
TechnologyId = dense_rank() over (order by FeatureName,FeatureValue)
+ (select max(TechnologyId) from #partsfeature)
from
(
values
(3900,'grail','51V',NULL),
(5442,'compress','30v',NULL),
(7791,'AC','59V',NULL),
(8321,'Angit','50V',NULL)
) s (PartId,FeatureName,FeatureValue,TechnologyId)
Expected Result
PartId FeatureName FeatureValue TechnologyId
7791 AC 59V 4
8321 Angit 50V 5
5442 compress 30v 6
3900 grail 51V 2