将%S替换为基于特征名称和部分的特征字符串上的特征值

Question

我需要编写一个select语句，将FeatureString列中的一个D1字符串替换为存储在给定PartID的同一个表中的实际值。

使用以下示例数据：

+--------+--------------+---------------+------------------------+
| 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) |
+--------+--------------+---------------+------------------------+

我想检索以下FeatureValueString：

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

解释

我需要将FeatureString的D9部分替换为存储在相应的FeatureName - FeatureValue列组合中的值。

最后一个值为7791的D13是一个特殊情况，因为它只需要存储在表中的两个值。这些是AC和Boil。Temp的值在返回的FeatureString中不是必需的。

样本数据

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

预期结果的

截图

可以有三个以上的特征。

Answer 1

一种解决办法是：

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;

另一个使用递归替换的方法：

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在线演示