SQLTeam.com | Weblogs | Forums

How to pivot data values On Features based on parts Exist on table inputdata?

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

You can do dynamic pivot

    DECLARE @PartCColumns VARCHAR(MAX),
			@PartXColumns VARCHAR(MAX),
			@ColsList VARCHAR(MAX),
			@SQLStatement varchar(max)

	DECLARE @Cols TABLE (Head VARCHAR(MAX)) 
  
	INSERT @Cols (Head)  
	SELECT FeatureName  
	FROM #Features
	order by FeatureId
  
	SELECT  @ColsList = COALESCE(@ColsList + ',max([', ',max([') + Head + 'PartC])  as [' + Head + 'PartC]'  + ', max([' + Head + 'PartX]) as [' + Head + 'PartD]' ,
			@PartCColumns = COALESCE(@PartCColumns + ',[', '[') + Head + 'PartC]' ,
		    @PartXColumns = COALESCE(@PartXColumns + ',[', '[') + Head + 'PartX]' 
	FROM @Cols t  
	

set @SQLStatement = 'SELECT PartIdC, PartIdX  ' +  @ColsList + '
	from
		( Select PartIdC, PartIdX, case when i.PartIDC = p.PartID then FeatureValue else null end as FeatureValue, 
				case when i.PartIdX = p.PartID then FeatureValue else null end as xFeatureValue,  
				f.FeatureName + ''PartC'' as PartC, f.FeatureName + ''PartX'' as PartX 
		  from #Features f
			join #PartsData p
				on f.FeatureID = p.FeatureID
			left join #InputData i
				on i.PartIDC = p.PartID
				or i.PartIdX = p.PartID) t
		PIVOT (Max(FeatureValue) FOR PartC IN (' + @PartCColumns + ')) PartC
		PIVOT (Max(xFeatureValue) FOR PartX IN (' + @PartXColumns + ')) PartX
		group by PartIdC, PartIdX  
		order by PartIdC, PartIdX'

	exec (@SQLStatement)