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
create table #features
(
FeatureId int,
FeatureName nvarchar(50),
DisplayOrder int
)
insert into #features(FeatureId,FeatureName,DisplayOrder)
values
(124003,'Supply',1),
(157301,'Volt',2),
(980012,'Resistor',3),
(887901,'Capacity',4)
create table #partsdata
(
PartId int,
FeatureId int,
FeatureValue nvarchar(20)
)
insert into #partsdata(PartId,FeatureId,FeatureValue)
values
(1290,124003,'40V'),
(1290,157301,'50k'),
(1290,980012,'90A'),
(1290,887901,'100V'),
(1590,124003,'30V'),
(1590,157301,'70k'),
(1590,980012,'20A'),
(1590,887901,'80V')
CREATE TABLE #InputData
(
PartIdC INT,
PartIdX int
)
insert into #InputData(PartIdC,PartIdX)
values
(1290,1590)
ExpectedResult
PartIdc PartIdx Supply-PartC Supply-PartX Volt-PartC Volt-PartX Resistor-PartC Resistor-PartX Capacity-PartC Capacity-PartX
1290 1590 40V 30V 50k 70k 90A 20A 100V 80V