sql - T-SQL如何将多个子字符串转换为新值

Question

首先，对不起，因为我不知道如何为我的问题命名。

我的情况是，我有1个具有这种格式的查找表:

+----+-----------+------------+
| ID | Fruit     |  Color     |
+----+-----------+------------+
|  1 | Banana    | Yellow     |
|  2 | Apple     | Red        |
|  3 | Blueberry | NotYetBlue |
+----+-----------+------------+

我的主表是这样的:

+-------+------------------------+------------+
| MixID |        Contains        | MixedColor |
+-------+------------------------+------------+
|     1 | Banana                 |            |
|     2 | Apple:Blueberry        |            |
|     3 | Banana:Apple:Blueberry |            |
+-------+------------------------+------------+

我想在第一个表上进行查找，并按如下所示填写MixedColor列:

+-------+------------------------+-----------------------+
| MixID |        Contains        |      MixedColor       |
+-------+------------------------+-----------------------+
|     1 | Banana                 | Yellow                |
|     2 | Apple:Blueberry        | Red:NotYetBlue        |
|     3 | Banana:Apple:Blueberry | Yellow:Red:NotYetBlue |
+-------+------------------------+-----------------------+

任何帮助将不胜感激。

谢谢

Answer 1

我同意理想情况下应该更改表结构。但是，您可以通过以下方式获得想要的东西:

SELECT   MIXID, [CONTAINS],
STUFF((
          SELECT ':' + Color
          FROM Table1 a
          WHERE ':'+b.[Contains]+':' LIKE '%:'+a.Fruit+':%'
                FOR XML PATH('')
            ), 1, 1, '') AS Color
FROM Table2 b
GROUP BY MIXID, [CONTAINS]

演示: SQL Fiddle