How to add feature value without prevent group data based on itemid pivot table?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  February 4, 2020 at 3:36 am

  #3722812

  How to add feature value without prevent group data based on itemid pivot table?

  I work on SQL server 2012

  I make pivot table based on itemId

  it work good but after add featurevalue data repeated

  and not grouping

  How to add Featurevalue without prevent repeated data on pivot table ?

  desired result
  ItemCode  IPN PartnerName CustomerNameFanRefrigatortemprature  FeatureValue
  1  1233 Saico      Michel1        2                 1            1234          
  2  5433 Mbaby      Michel0        1                 0            7777
  3  44333 sadeoMany    Michel1        0                 1            88888
  What I have tried:
  
  create table #InputData
  (
  CustomerID uniqueidentifier
  
  )
  insert into #InputData values ('0ce19920-f0ca-433c-abb1-4e84d52b618b')
  
  create table #customers
  (
  CustomerID uniqueidentifier,
  CustomerName  nvarchar(200)
  
  )
  insert into #customers 
  values
  ('0ce19920-f0ca-433c-abb1-4e84d52b618b','Michel'),
  ('188b8053-18c0-4092-955e-962f54485e43','Jakson')
  
  create table #FeatureType
  (
  FeatureId int,
  FeatureName  nvarchar(200)
  
  )
  insert into #FeatureType 
  values
  (1,'temprature'),
  (2,'Fan'),
  (3,'Refrigator')
  
  create table #Items
  (
  ItemId int,
  IPN  nvarchar(200),
  PartnerPart  nvarchar(200),
  PartnerName nvarchar(100)
  )
  insert into #Items 
  values
  (1,'1233','Mobilic','Saico'),
  (2,'5433','Saldom','Mbaby'),
  (3,'44333','Silicon','sadeoMany')
  
  create table #ItemFeatures
  (
  ItemFeatureId int,
  ItemId  int,
  FeatureId int,
  CustomerId uniqueidentifier,
  FeatureValue  nvarchar(50)
  )
  insert into #ItemFeatures 
  values
  (1,1,1,'0ce19920-f0ca-433c-abb1-4e84d52b618b','1234'),
  (2,1,2,'0ce19920-f0ca-433c-abb1-4e84d52b618b','4333'),
  (3,1,3,'0ce19920-f0ca-433c-abb1-4e84d52b618b','55555'),
  (4,1,3,'0ce19920-f0ca-433c-abb1-4e84d52b618b','66666'),
  (5,2,3,'0ce19920-f0ca-433c-abb1-4e84d52b618b','7777'),
  (6,3,1,'0ce19920-f0ca-433c-abb1-4e84d52b618b','88888'),
  (7,3,2,'0ce19920-f0ca-433c-abb1-4e84d52b618b','99999')
  
  DECLARE @Columns as VARCHAR(MAX)
  SELECT @Columns =
  COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
  FROM
  --distinct FT.FeatureName 
  (select  distinct FT.FeatureName  from #InputData Feat inner join #ItemFeatures ItemF
  on ItemF.CustomerId=Feat.CustomerId INNER join #FeatureType FT on ItemF.FeatureId=FT.FeatureId
  
     ) AS B
     ORDER BY B.FeatureName
  
  DECLARE @SQLs as VARCHAR(MAX)
  
  SET @SQLs = 'SELECT ItemCode, IPN,PartnerName,CustomerName,FeatureValue ' + @Columns + '
  FROM
  (
    select F.ItemId,F.ItemId as ItemCode,I.IPN,I.PartnerName,I.PartnerPart,c.CustomerName,t.FeatureName,F.FeatureValue  from #InputData Itm 
   inner join #ItemFeatures F on F.CustomerId=Itm.CustomerId  
   inner join #Items I on I.ItemID=F.ItemId
   inner join #FeatureType T on T.FeatureId=F.FeatureId 
   inner join #customers c on c.CustomerID=F.CustomerID 
  ) as PivotData
  PIVOT
  (
     COUNT(ItemId)
     FOR FeatureName IN (' + @Columns + ')
  ) AS PivotResult
  ORDER BY CustomerName'
  
  EXEC(@SQLs)
  

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  February 5, 2020 at 4:10 am

  #3723167

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Kathi Kellenberger

  SSChasing Mays

  Points: 601

  More actions

  February 5, 2020 at 10:05 am

  #3723221

  Looks like you are missing a comma after the FeatureValue column.

  SET @sqls = 'SELECT ItemCode, IPN,PartnerName,CustomerName,FeatureValue, ' + @Columns + '

  Try that and see if it works.