I work on sql server 2019 i can't make pivot for feature values based on feature name .
i need to apply pivot dynamically because may be tomorrow add new features so it will depend on features on table .
for original part id i have 4 features as
ProgramMemoryOriginal,NumberCoresOriginal,NumberADCsOriginal,NumberDACsOriginal
for RecomendationPartId i have 4 features as
ProgramMemoryRecomended,NumberCoresRecomended,NumberADCsRecomended,NumberDACsRecomended
i need to pivot every feature value under every feature name so feature name will display horizontally
if features not exist or remaining for parts as part id 2081978 then it will display as null
code as below
CREATE TABLE #partsrecomendation([OrignalPartId] [int] NOT NULL,[RecomendationPartId] [int] NOT NULL) ON [PRIMARY]GOINSERT #partsrecomendation ([OrignalPartId], [RecomendationPartId]) VALUES (20819956,1595758)INSERT #partsrecomendation ([OrignalPartId], [RecomendationPartId]) VALUES (20819770,2081978)CREATE TABLE #TechnologyPlPartsFeaturValues([PartID] [int] NOT NULL,[FeatureID] [int] NULL,[FeatureName] [nvarchar](508) NOT NULL,[FeatureValue] [nvarchar](500) NOT NULL) ON [PRIMARY]GOINSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819956, 1500500021, N'ProgramMemoryOriginal', N'FLASH')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819956, 1500500011, N'NumberCoresOriginal', N'1')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819956, 1500500066, N'NumberADCsOriginal', N'2')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819956, 1500500081, N'NumberDACsOriginal', N'1')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (1595758, 1500500021, N'ProgramMemoryRecomended', N'Light')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (1595758, 1500500011, N'NumberCoresRecomended', N'12')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (1595758, 1500500066, N'NumberADCsRecomended', N'75')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (1595758, 1500500081, N'NumberDACsRecomended', N'90')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819770, 1500500021, N'ProgramMemoryOriginal', N'Silicon')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819770, 1500500011, N'NumberCoresOriginal', N'509')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819770, 1500500066, N'NumberADCsOriginal', N'701')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (20819770, 1500500081, N'NumberDACsOriginal', N'208')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (2081978, 1500500021, N'Program Memory TypeRecomended', N'Electricity')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (2081978, 1500500011, N'NumberCoresRecomended', N'72')INSERT #TechnologyPlPartsFeaturValues ([PartID], [FeatureID], [FeatureName], [FeatureValue]) VALUES (2081978, 1500500066, N'NumberADCsRecomended', N'925')
expected result