ahmed salah

ahmed salah

  • 1.9k
  • 196
  • 4.9k

How to modify query to get correct technology id?

Jun 11 2022 6:27 PM

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

 


Answers (2)