How to make pivot to feature values based on part ca and x exist on input data ?

Question

Post reply

How to make pivot to feature values based on part ca and x exist on input data ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

December 17, 2020 at 10:22 am

Go to Answer

#3821524

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

Steve Collins

SSCrazy

Points: 2157

December 18, 2020 at 4:50 pm

Answer

#3822116

declare 
  @sql_prefix   nvarchar(max)=N'select id.PartIdC, id.PartIdX,',
  @sql_suffix   nvarchar(max)=
  N'from #InputData id
     left join #partsdata p1 on id.PartIdC=p1.PartId
     left join #features f1 on p1.FeatureId=f1.FeatureId
     left join #partsdata p2 on id.PartIdX=p2.PartId
     left join #features f2 on p2.FeatureId=f2.FeatureId
    group by id.PartIdC, id.PartIdX;',
  @sql_string   nvarchar(max),
  @sql          nvarchar(max);

declare 
  @fn_prefix    nvarchar(max)=N'max(iif(f1.FeatureName=''',
  @fn_string1    nvarchar(max)=N''', p1.FeatureValue, null)) [',
  @fn_string2    nvarchar(max)=N'-PartC], max(iif(f2.FeatureName=''',
  @fn_string3    nvarchar(max)=N''', p2.FeatureValue, null)) [',
  @fn_suffix    nvarchar(max)=N'-PartX]';

with
seq_features_cte(FeatureId, FeatureName, seq_num) as (
    select f.FeatureId, f.FeatureName, row_number() over (order by f.DisplayOrder)
    from #features f
    where exists (select 1
                  from #partsdata p
                  where f.FeatureId=p.FeatureId)),
features_string_cte(sql_string) as (
    select stuff((select concat(', ', @fn_prefix, 
                                sf.FeatureName, @fn_string1,
                                sf.FeatureName, @fn_string2,
                                sf.FeatureName, @fn_string3,
                                sf.FeatureName, @fn_suffix)
                  from seq_features_cte sf
                  order by sf.seq_num
                  for xml path('')),1,1,''))
select @sql=concat(@sql_prefix, sql_string, @sql_suffix)
from features_string_cte;
--print (@sql);

exec sp_sqlexec @sql;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Here's a non-dynamic query to produce the output. If there's a variable number of features then it would need to be dynamic.

select id.PartIdC, id.PartIdX,
       max(iif(f1.FeatureName='Supply', p1.FeatureValue, null)) [Supply-PartC],
       max(iif(f2.FeatureName='Supply', p2.FeatureValue, null)) [Supply-PartX],
       max(iif(f1.FeatureName='Volt', p1.FeatureValue, null)) [Volt-PartC],
       max(iif(f2.FeatureName='Volt', p2.FeatureValue, null)) [Volt-PartX],
       max(iif(f1.FeatureName='Resistor', p1.FeatureValue, null)) [Resistor-PartC],
       max(iif(f2.FeatureName='Resistor', p2.FeatureValue, null)) [Resistor-PartX],
       max(iif(f1.FeatureName='Capacity', p1.FeatureValue, null)) [Capacity-PartC],
       max(iif(f2.FeatureName='Capacity', p2.FeatureValue, null)) [Capacity-PartX]
from #InputData id
     left join #partsdata p1 on id.PartIdC=p1.PartId
     left join #features f1 on p1.FeatureId=f1.FeatureId
     left join #partsdata p2 on id.PartIdX=p2.PartId
     left join #features f2 on p2.FeatureId=f2.FeatureId
group by id.PartIdC, id.PartIdX;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

thank you for reply

this is static i need it dynamic because sometimes i don't know features on table

so if possible i need it depend on features exist on table partsdata

and also this features always changed so can't use it as if condition

so how to do that please ?