How to make pivot for features dynamically based on feature name and feature value ?

Question

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]  
  
GO  
INSERT #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]  
  
GO  
  
INSERT #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

Answer

Hi @ahmed salah
Please check the query below:

DECLARE @sql VARCHAR(MAX)  
DECLARE @Orignal_Columns VARCHAR(MAX)  
DECLARE @Recomend_Columns VARCHAR(MAX)  
DECLARE @All_Columns VARCHAR(MAX)  
  
SELECT @Orignal_Columns=ISNULL(@Orignal_Columns+',','')+QUOTENAME(FeatureName)  
FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.OrignalPartId=T.PartID  
GROUP BY FeatureName  
--PRINT @Orignal_Columns  
SELECT @Recomend_Columns=ISNULL(@Recomend_Columns+',','')+QUOTENAME(FeatureName)  
FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.RecomendationPartId=T.PartID  
GROUP BY FeatureName  
--PRINT @Recomend_Columns  
SELECT @All_Columns=ISNULL(@All_Columns+',','')+QUOTENAME(FeatureName)  
FROM #TechnologyPlPartsFeaturValues  
GROUP BY FeatureID,FeatureName  
ORDER BY FeatureID  
--PRINT @All_Columns  
SET @sql=N'  
;WITH Orignal AS  
(  
 SELECT * FROM (SELECT OrignalPartId,[FeatureName], [FeatureValue]   
                FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.OrignalPartId=T.PartID)O  
 PIVOT(MAX(FeatureValue)FOR FeatureName IN('+@Orignal_Columns+'))P  
),Recomend AS  
(  
 SELECT * FROM (SELECT RecomendationPartId,[FeatureName], [FeatureValue]   
                FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.RecomendationPartId=T.PartID)R  
 PIVOT(MAX(FeatureValue)FOR FeatureName IN('+@Recomend_Columns+'))P  
)  
SELECT P.*,'+@All_Columns+'  
FROM #partsrecomendation P JOIN Orignal O ON P.OrignalPartId=O.OrignalPartId  
                           JOIN Recomend R ON P.RecomendationPartId=R.RecomendationPartId  
'  
EXEC (@sql)

Output:

This output is a little bit different from your results, but I think it is caused by the insert datas, as I commented above.

Best regards,
LiHong

How to make pivot for features dynamically based on feature name and feature value ?

1 answer