根據特徵名稱和 Partid 將 %S 替換為特徵字元串上的特徵值

我需要編寫一個 select 語句，將列%S中的字元串替換為FeatureString儲存在同一表中給定的實際值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) |
+--------+-------------------------+

解釋

我需要用儲存在相應列組合中的值替換%S部分。FeatureString``FeatureName``FeatureValue

最後一個PartID帶有值7791的情況有點特殊，因為它只需要儲存在表中的兩個值。這些是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)')

預期結果截圖

可以有三個以上的特徵。

一種解決方案：

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 線上展示

引用自：https://dba.stackexchange.com/questions/313126