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

ahmed salah 3,216 Reputation points

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


for RecomendationPartId i have 4 features as


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  
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  
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


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,854 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,560 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CosmogHong-MSFT 23,556 Reputation points Microsoft Vendor

    Hi @ahmed salah
    Please check the query below:

    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)  


    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,

    0 comments No comments