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

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

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)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar4y ago
Accepted answer
1. DECLARE @sql VARCHAR(MAX)
2. DECLARE @Orignal_Columns VARCHAR(MAX)
3. DECLARE @Recomend_Columns VARCHAR(MAX)
4. DECLARE @All_Columns VARCHAR(MAX)
6. SELECT @Orignal_Columns=ISNULL(@Orignal_Columns+',','')+QUOTENAME(FeatureName)
7. FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.OrignalPartId=T.PartID
8. GROUP BY FeatureName
9. --PRINT @Orignal_Columns
10. SELECT @Recomend_Columns=ISNULL(@Recomend_Columns+',','')+QUOTENAME(FeatureName)
11. FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.RecomendationPartId=T.PartID
12. GROUP BY FeatureName
13. --PRINT @Recomend_Columns
14. SELECT @All_Columns=ISNULL(@All_Columns+',','')+QUOTENAME(FeatureName)
15. FROM #TechnologyPlPartsFeaturValues
16. GROUP BY FeatureID,FeatureName
17. ORDER BY FeatureID
18. --PRINT @All_Columns
19. SET @sql=N'
20. ;WITH Orignal AS
21. (
22. SELECT * FROM (SELECT OrignalPartId,[FeatureName], [FeatureValue]
23. FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.OrignalPartId=T.PartID)O
24. PIVOT(MAX(FeatureValue)FOR FeatureName IN('+@Orignal_Columns+'))P
25. ),Recomend AS
26. (
27. SELECT * FROM (SELECT RecomendationPartId,[FeatureName], [FeatureValue]
28. FROM #partsrecomendation P JOIN #TechnologyPlPartsFeaturValues T ON P.RecomendationPartId=T.PartID)R
29. PIVOT(MAX(FeatureValue)FOR FeatureName IN('+@Recomend_Columns+'))P
30. )
31. SELECT P.*,'+@All_Columns+'
32. FROM #partsrecomendation P JOIN Orignal O ON P.OrignalPartId=O.OrignalPartId
33. JOIN Recomend R ON P.RecomendationPartId=R.RecomendationPartId
34. '
35. EXEC (@sql)