How to get feature key and feature value separated $ based on part id ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3070

  More actions

  October 28, 2021 at 1:17 am

  #3945324

  I work on sql server 2012 i need to get featurekey and feature value separated $

  Based on partid but i don't know how o do that by select sql query ?

  expected result as below

   

  

  sample data
  
   create table #PartsFeature
   (
   PartId int,
   Featurekey nvarchar(200),
   FeatureValue nvarchar(200),
   )
   insert into #PartsFeature(PartId,Featurekey,FeatureValue)
   values
   (1550,'Botato','Yellow'),
   (1550,'Mango','Red'),
   (1550,'dates','Black'),
   (1600,'Rice','white'),
   (1600,'macrona','Red'),
   (1600,'chicken','Yellow'),
   (1700,'Guava','Yellow'),
   (1700,'grapes','Green'),
   (1700,'FIG','Red')

• pietlinden

  SSC Guru

  Points: 63198

  More actions

  October 28, 2021 at 2:56 am

  #3945345

  Upgrade and use STRING_AGG?

  use tempdb;
  go
  
   create table #PartsFeature
   (
   PartId int,
   Featurekey nvarchar(200),
   FeatureValue nvarchar(200),
   );
   go
  
   insert into #PartsFeature(PartId,Featurekey,FeatureValue)
   values
   (1550,'Botato','Yellow'),
   (1550,'Mango','Red'),
   (1550,'dates','Black'),
   (1600,'Rice','white'),
   (1600,'macrona','Red'),
   (1600,'chicken','Yellow'),
   (1700,'Guava','Yellow'),
   (1700,'grapes','Green'),
   (1700,'FIG','Red');
  
   SELECT PartID,
  FeatureKeys = STRING_AGG(FeatureKey,'$'),
  FeatureValues = STRING_AGG(FeatureValue,'$')
   FROM #PartsFeature
   GROUP BY PartID;

  In my grandpa's days they used stuff like this:

  SELECT  a.UserID, 
          SUBSTRING(d.Addresses,1, LEN(d.Addresses) - 1) AddressList
  FROM
          (
              SELECT DISTINCT UserID
              FROM tableName
          ) a
          CROSS APPLY
          (
              SELECT [AddressLine1] + ', ' 
              FROM tableName AS B 
              WHERE A.UserID = B.UserID 
              FOR XML PATH('')
          ) D (Addresses)

  Or, if you gotta use 2012, this should work:

  SELECT PartID,
  	FeatureValues2 = RIGHT(x.FeatureValues2,LEN(x.FeatureValues2)-1)
  FROM
  ( SELECT PartID,
  	FeatureValues2 =  ( SELECT '$' + pf2.FeatureValue 
  					FROM #PartsFeature pf2 
  					WHERE pf2.partID = pf.PartID
  					FOR XML PATH('')
  					)
   FROM #PartsFeature pf
   GROUP BY PartID ) x;

  • This reply was modified 2 years, 11 months ago by  pietlinden.