Replace %S with Feature Value on Feature String Based on Feature Name and Partid

Question

I need to write a select statement that will replace a %S string in the FeatureString column with the actual values stored in the same table for a given PartID.

With the following example data:

+--------+--------------+---------------+------------------------+
| PartID | FeatureName | FeatureValue | FeatureString |
+--------+--------------+---------------+------------------------+
| 1211 | AC | 5V | AC(%S)Boil(%S)Temp(%S) |
| 1211 | Boil | 10v | AC(%S)Boil(%S)Temp(%S) |
| 1211 | Temp | 5V | AC(%S)Boil(%S)Temp(%S) |
+--------+--------------+---------------+------------------------+

I would like to retrieve the following FeatureValueString:

+--------+-------------------------+
| PartID | FeatureName |
+--------+-------------------------+
| 1211 | AC(5V)Boil(10v)Temp(5V) |
+--------+-------------------------+

Explanation

I need to replace the %S part of the FeatureString with the values that are stored in the corresponding FeatureName - FeatureValue column combinations.

The last PartID with the value 7791 is a bit of a special case, as it only requires two of the values stored in the table. These being AC and Boil. The value for Temp isn't required in the FeatureString returned.

Sample Data

 create table #partsfeature
 (
 PartId int,
 FeatureName varchar(300),
 FeatureValue varchar(300),
 FeatureString varchar(300)
 )
 insert into #partsfeature(PartId,FeatureName,FeatureValue,FeatureString)
 values
 (1211,'AC','5V','AC(%S)Boil(%S)Temp(%S)'),
 (1211,'Boil','10v','AC(%S)Boil(%S)Temp(%S)'),
 (1211,'Temp','5V','AC(%S)Boil(%S)Temp(%S)'),
 (2421,'grail','51V','Alc(%S)Coil(%S)grail(%S)'),
 (2421,'Coil','9V','Alc(%S)Coil(%S)grail(%S)'),
 (2421,'Alc','5V','Alc(%S)Coil(%S)grail(%S)'),
 (6211,'compress','33v','compress(%S)heat(%S)push(%S)'),
 (6211,'heat','90v','compress(%S)heat(%S)push(%S)'),
 (6211,'push','80v','compress(%S)heat(%S)push(%S)'),
 (5442,'compress','33v','compress(%S)heat()push(%S)'),
 (5442,'heat','90v','compress(%S)heat()push(%S)'),
 (5442,'push','80v','compress(%S)heat()push(%S)'),
 (7791,'AC','5V','AC(%S)Boil(%S)'),
 (7791,'Boil','10v','AC(%S)Boil(%S)'),
 (7791,'Temp','5V','AC(%S)Boil(%S)'),
 (8321,'Angit','50V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
 (8321,'Fan','9v','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
 (8321,'Hot','3V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
 (8321,'Wether','12V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)')

Screenshot of Expected Results

replace %S with Feature value

There can be more than three features.

Answer 1

One solution:

SELECT 
 P.*, 
 FVS.FeatureValueString 
FROM #partsfeature AS P
CROSS APPLY
(
 SELECT 
 FeatureValueString =
 STRING_AGG(
 CONCAT(
 P2.FeatureName, 
 '(',
 IIF(
 CHARINDEX(
 P2.FeatureName + '(%S)', 
 P2.FeatureString) > 0,
 P2.FeatureValue,
 ''),
 ')'), 
 '')
 WITHIN GROUP (
 ORDER BY CHARINDEX(P2.FeatureName, P2.FeatureString))
 FROM #partsfeature AS P2
 WHERE
 P2.PartId = P.PartId
 AND CHARINDEX(P2.FeatureName, P2.FeatureString) > 0
) AS FVS;

Another using recursive replacement:

WITH R AS
(
 SELECT DISTINCT 
 P.PartId, 
 P.FeatureString
 FROM #partsfeature AS P
 UNION ALL
 SELECT
 P.PartId,
 CONVERT(varchar(300),
 REPLACE(
 R.FeatureString, 
 P.FeatureName + '(%S)', 
 P.FeatureName + '(' + P.FeatureValue + ')'))
 FROM R
 JOIN #partsfeature AS P
 ON P.PartId = R.PartId
 AND R.FeatureString LIKE '%' + P.FeatureName + '([%]S)%'
 AND R.FeatureString NOT LIKE '%([%]S)%' + P.FeatureName + '%'
)
SELECT
 P.*,
 FeatureValueString = R.FeatureString
FROM R
JOIN #partsfeature AS P
 ON P.PartId = R.PartId
WHERE 
 R.FeatureString NOT LIKE '%([%]S)%';

db<>fiddle online demo