How to make select query get featurename pipe featurevalue based on partid ?

Question

I work on sql server 2017 i can't make select query for featurename pipe feature value as FeaturesNameValueCollections
based on partid .
as example partid 1211 have 3 features AC,Boil,Temp and these features for partid 1211 have 3 values 5v,10v,5v
then i will arrange it based on first charachter as AC(5v)|Boil(10v)|Temp(5v)
featurename(featurevalue)|featurename(featurevalue)|featurename(featurevalue) arranged alphaptic

as sample
create table #partsfeature
(
PartId int,
FeatureName varchar(300),
FeatureValue varchar(300)
)

insert into #partsfeature(PartId,FeatureName,FeatureValue)  
values  
(1211,'AC','5V'),  
(1211,'Boil','10v'),  
(1211,'Temp','5V'),  
(2421,'grail','51V'),  
(2421,'Coil','9V'),  
(2421,'Alc','5V'),  
(6211,'compress','33v'),  
(6211,'heat','90v'),  
(6211,'push','80v')  

expected result will be

Answer 1

Hi，@ahmed salah

Welcome to Microsoft T-SQL Q&A Forum!

Please try the following code:

;with cte as  
(  
 SELECT P.PartId,  
 STUFF((SELECT ' | ' + [FeatureName] + '( '+ [FeatureValue] + ' )'  
 FROM  #partsfeature A  
 WHERE A.PartId=P.PartId  
 Order by featureName  
FOR XML PATH('')),1,2,'') AS FeaturesValueCollection   
FROM #partsfeature P   
GROUP BY P.PartId  
  
)  
select c.PartId,p.FeatureName ,p.FeatureValue ,FeaturesValueCollection  FROM CTE c  
inner join #partsfeature p on p.PartId=c.PartId  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try a query:

select *,  
    (select string_agg(concat(FeatureName, '(', FeatureValue, ')'), '|') within group (order by FeatureName)   
     from #partsfeature where PartId = p.PartId) as FeaturesValueCollection  
from #partsfeature p  

Answer 3

create table #partsfeature  
(  
PartId int,  
FeatureName varchar(300),  
FeatureValue varchar(300)  
)  
  
 insert into #partsfeature(PartId,FeatureName,FeatureValue)  
 values  
 (1211,'AC','5V'),  
 (1211,'Boil','10v'),  
 (1211,'Temp','5V'),  
 (2421,'grail','51V'),  
 (2421,'Coil','9V'),  
 (2421,'Alc','5V'),  
 (6211,'compress','33v'),  
 (6211,'heat','90v'),  
 (6211,'push','80v')  
  
;with mycte as (  
select  PartId,FeatureName, FeatureValue,FeatureName+'('+FeatureValue +')' Data   
from    #partsfeature  
   
)  
   
,mycte2 as (  
SELECT PartId,FeatureName, FeatureValue,  (SELECT  Vals =  JSON_QUERY(  
(  
SELECT Cast(Data as varchar(30)) Data   
FROM mycte t2 WHERE t2.PartId = t1.PartId     
FOR JSON PATH )  
)    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER  
)   jsCol  
FROM mycte t1  
Group by PartId,FeatureName, FeatureValue)  
   
   
select m.PartId,m.FeatureName, m.FeatureValue,   
Concat(JSON_VALUE (jsCol,'$.Vals[0].Data'),  
'|'+JSON_VALUE (jsCol,'$.Vals[1].Data'),  
'|'+JSON_VALUE (jsCol,'$.Vals[2].Data'),  
'|'+JSON_VALUE (jsCol,'$.Vals[3].Data') )  FeaturesValueCollections  
   
from mycte2 m join #partsfeature p   
on m.PartId=p.PartId   
and m.FeatureName = p.FeatureName   
and m.FeatureValue = p.FeatureValue  
  
  
drop table #partsfeature  
/*  
PartId	FeatureName	FeatureValue	FeaturesValueCollections  
1211	AC	5V	AC(5V)|Boil(10v)|Temp(5V)  
1211	Boil	10v	AC(5V)|Boil(10v)|Temp(5V)  
1211	Temp	5V	AC(5V)|Boil(10v)|Temp(5V)  
2421	Alc	5V	grail(51V)|Coil(9V)|Alc(5V)  
2421	Coil	9V	grail(51V)|Coil(9V)|Alc(5V)  
2421	grail	51V	grail(51V)|Coil(9V)|Alc(5V)  
6211	compress	33v	compress(33v)|heat(90v)|push(80v)  
6211	heat	90v	compress(33v)|heat(90v)|push(80v)  
6211	push	80v	compress(33v)|heat(90v)|push(80v)  
  
*/