How To display differenet feature values on one row separated stick if it multiple difference ?

Question

I work on SQL server 2012 I face issue : I can't get all different value Feature one one row result

separated by sticky if it multiple difference

if one different then no need stick.

where c have value and x have value but both not equal each other

so How can i do that Please ?

    create table #replace  
(  
PartIdc int,  
PartIdx int,  
)  
insert into #replace(PartIdc,PartIdx)  
values  
(1211,1300),  
(2000,2200),  
(3000,3100),  
(4150,4200)  
  
create table #FeatureNameandValues  
(  
PartId int,  
FeatueName nvarchar(20),  
FeaatureValue int  
)  
insert into #FeatureNameandValues(PartId,FeatueName,FeaatureValue)  
values  
(1211,'Weight',5),  
(2000,'Tall',20),  
(3000,'Weight',70),  
(4150,'Tall',190),  
(1211,'Tall',80),  
(1300,'Weight',10),  
(3100,'Size',150),  
(4200,'Tall',130),  
(1300,'Tall',20)  

Expected Result

Tall (80-20) | Weight(5-10) | Tall(190-130)  

and file excel attached ;

http://www.mediafire.com/file/mxyr8wr9k98za7o/ExplainReport.xlsx/file

Answer 1

;With cte As
(Select f1.PartId, f1.FeatueName, 
  f1.FeatueName + ' (' + Cast(f1.FeaatureValue As varchar(10)) + '-' + Cast(f2.FeaatureValue As varchar(10)) + ')' As ValueRange
From #FeatureNameandValues f1
Inner Join #replace r On f1.PartId = r.PartIdc
Inner Join #FeatureNameandValues f2 On f2.PartId = r.PartIdx And f1.FeatueName = f2.FeatueName)
Select Stuff(
    (Select ' | ' + ValueRange From cte Order By PartId, FeatueName 
    For XML Path(''),Type)
    .value('text()[1]','varchar(max)'),1,3,'');

Tom

Answer 2

Hi @ahmed salah ，

Please refer to:

   create table #replace  
 (  
 PartIdc int,  
 PartIdx int,  
 )  
 insert into #replace(PartIdc,PartIdx)  
 values  
 (1211,1300),  
 (2000,2200),  
 (3000,3100),  
 (4150,4200)  
      
 create table #FeatureNameandValues  
 (  
 PartId int,  
 FeatueName nvarchar(20),  
 FeaatureValue int  
 )  
 insert into #FeatureNameandValues(PartId,FeatueName,FeaatureValue)  
 values  
 (1211,'Weight',5),  
 (2000,'Tall',20),  
 (3000,'Weight',70),  
 (4150,'Tall',190),  
 (1211,'Tall',80),  
 (1300,'Weight',10),  
 (3100,'Size',150),  
 (4200,'Tall',130),  
 (1300,'Tall',20)  
  
;with cte  
as(select FeatueName,PartIdc,PartIdx from #replace cross apply (select distinct FeatueName from #FeatureNameandValues)t  
)  
,cte2 as  
(select c.FeatueName,c.PartIdc,c.PartIdx,case when c.PartIdc=f.PartId and c.FeatueName=f.FeatueName then f.FeaatureValue end FeaatureValueC,  
       case when c.PartIdx=f.PartId and c.FeatueName=f.FeatueName then f.FeaatureValue  end FeaatureValueX,  
	   row_number() over(partition by c.PartIdc,c.FeatueName order by c.PartIdc,c.FeatueName) rn   
from cte c  
left join #FeatureNameandValues f  
on (c.PartIdc=f.PartId and c.FeatueName=f.FeatueName) or (c.PartIdx=f.PartId and c.FeatueName=f.FeatueName))  
,cte3 as  
(select a.FeatueName,a.PartIdc,a.PartIdx,a.FeaatureValueC,a.FeaatureValueX FeaatureValueX1 ,a.rn,  
       lead(b.FeaatureValueX,1) over(partition by a.PartIdc,a.FeatueName order by a.PartIdc,a.FeatueName) FeaatureValueX2  
from cte2 a  
left join cte2 b  
on a.FeatueName=b.FeatueName and a.PartIdc=b.PartIdc and a.rn=b.rn)  
  
select  FeatueName,PartIdc,PartIdx,FeaatureValueC,case when FeaatureValueX1=150 then FeaatureValueX1 else FeaatureValueX2 end FeaatureValueX  
from cte3  
where rn<2  
order by PartIdc,FeatueName  
  
drop table #replace  
drop table #FeatureNameandValues  

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.