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

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