How to display Text Unit only one time if it repeated for same Feature when do stuff?

Question

I work with SQL Server 2012 and face an issue: I can't display Text Unit only one time where it repeated for feature using Stuff.

What I need is when Text Unit is repeated for same feature, then no need to repeat it - only display it once.

In my case, I face issue that I can't prevent repeat Text Unit when It be same Text Unit for same Feature.

Voltage | Voltage | Voltage ONLY one Voltage display .

CREATE TABLE #FinalTable
(
    PartID INT,
    DKFeatureName NVARCHAR(100),
    TextUnit NVARCHAR(100),
    StatusId INT
)

INSERT INTO #FinalTable (PartID, DKFeatureName, TextUnit, StatusId)
VALUES
(1211, 'PowerSupply', 'Voltage', 3),
(1211, 'PowerSupply', 'Voltage', 3),
(1211, 'PowerSupply', 'Voltage', 3)
 
SELECT 
    PartID, DKFeatureName, 
    COUNT(PartID) AS CountParts,
    TextUnit = STUFF ((SELECT ' | ' + TextUnit  
                       FROM #FinalTable b  
                       WHERE b.PartID = a.PartID 
                         AND a.DKFeatureName = b.DKFeatureName 
                         AND StatusId = 3 
                       FOR XML PATH('')), 1, 2, ' ')  
INTO 
    #getUnitsSticky 
FROM 
    #FinalTable a  
GROUP BY 
    PartID, DKFeatureName
HAVING
    (COUNT(PartID) > 1) 

SELECT * 
FROM #getUnitsSticky

Expected result is :

Voltage

Incorrect result or result I don't need is as below :

Voltage|Voltage|Voltage

Answer 1

TomC's answer is basically correct. However, when using this method with SQL Server, it is usually more efficient to get the rows in a subquery and then use stuff() in the outer query. That way, the values in each row are processed only once.

So:

SELECT PartID, DKFeatureName, CountParts,
       STUFF( (SELECT ' | ' + TextUnit  
               FROM #FinalTable b  
               WHERE b.PartID = a.PartID AND
                     b.DKFeatureName = a.DKFeatureName AND
                     StatusId = 3 
               FOR XML PATH('')
              ), 1, 3, ' ') as TextUnit
INTO #getUnitsSticky 
FROM (SELECT PartID, DKFeatureName, COUNT(*) as CountParts
      FROM #FinalTable a  
      GROUP BY PartID, DKFeatureName
      HAVING COUNT(*) > 1
     ) a;

This also removes the leading space from the concatenated result.