How To display different features values for same Feature Name on one row separated By Stick?

Question

I work with SQL Server 2012 and I face an issue: I can't get all different values feature on one row result, separated by sticky if it multiple difference.

If only one is different, then no need stick.

I actually need to display feature value for part C and part X, where feature value for C is not equal to X and both are not equal to Null.

It will display on one row separated by stick if multiple differences.

So how can I do that?

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)

Final result:

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

Attached file explain :

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

score 0 · Answer 1 · answered Sep 30 '20 at 21:38

you can use stuff for do that like below

;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,'');

How To display different features values for same Feature Name on one row separated By Stick?

1 Answers1