ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 277.9k

How to pivot Feature values On Features based on parts Exist on table

Dec 17 2020 10:38 AM
I work on SQL server 2012 . I face issue I can't pivot features for every part based on display order .
 
I need to display Features as Pivot for Parts Based on Part Id Exist on Table partsdata
 
so I need to display features for multiple part on one row as pivot based on partc and partx exist on table
inputdata
 
I will give it partc and partx as inputdata table then i will pivot values for every features exist on table #features
 
arranged by display order feature
 
  1. create table #features  
  2.  (  
  3.  FeatureId  int,  
  4.  FeatureName nvarchar(50),  
  5.  DisplayOrder  int  
  6.  )  
  7.  insert into #features(FeatureId,FeatureName,DisplayOrder)  
  8.  values  
  9.  (124003,'Supply',1),  
  10.  (157301,'Volt',2),  
  11.  (980012,'Resistor',3),  
  12.  (887901,'Capacity',4)  
  13.  create table #partsdata  
  14.  (  
  15.  PartId  int,  
  16.  FeatureId int,  
  17.  FeatureValue nvarchar(20)  
  18.  )  
  19.  insert into #partsdata(PartId,FeatureId,FeatureValue)  
  20.  values  
  21.  (1290,124003,'40V'),  
  22.  (1290,157301,'50k'),  
  23.  (1290,980012,'90A'),  
  24.  (1290,887901,'100V'),  
  25.  (1590,124003,'30V'),  
  26.  (1590,157301,'70k'),  
  27.  (1590,980012,'20A'),  
  28.  (1590,887901,'80V')  
  29.  CREATE TABLE #InputData  
  30.  (  
  31.  PartIdC INT,  
  32.  PartIdX  int  
  33.  )  
  34.  insert into #InputData(PartIdC,PartIdX)  
  35.  values  
  36.  (1290,1590)  
  37.       
  38.  ExpectedResult  
    1. PartIdc PartIdx  Supply-PartC Supply-PartX Volt-PartC   Volt-PartX     Resistor-PartC   Resistor-PartX   Capacity-PartC  Capacity-PartX  
    2. 1290    1590          40V          30V        50k           70k              90A             20A               100V            80V  

 

Answers (1)