基于另一列替换列中的值

Question

我有下表：

+----+--------+------------+----------------------+
| ID |  Name  | To_Replace |       Replaced       |
+----+--------+------------+----------------------+
|  1 | Fruits | 1          | Fruits               |
|  2 | Apple  | 1-2        | Fruits-Apple         |
|  3 | Citrus | 1-3        | Fruits-Citrus        |
|  4 | Orange | 1-3-4      | Fruits-Citrus-Orange |
|  5 | Empire | 1-2-5      | Fruits-Apple-Empire  |
|  6 | Fuji   | 1-2-6      | Fruits-Apple-Fuji    |
+----+--------+------------+----------------------+

如何创建列Replaced？我考虑创建10个最大列(我知道嵌套级别不超过10个)，并从按'-‘拆分的每个子字符串中获取ID，然后将它们连接到Replaced中，但我认为有一个更简单的解决方案。

Answer 1

你可以试试这样的东西：

DECLARE @Data TABLE ( ID INT, [Name] VARCHAR(10), To_Replace VARCHAR(10) );
INSERT INTO @Data ( ID, [Name], To_Replace ) VALUES
( 1, 'Fruits', '1' ),
( 2, 'Apple', '1-2' ),
( 3, 'Citrus', '1-3' ),
( 4, 'Orange', '1-3-4' ),
( 5, 'Empire', '1-2-5' ),
( 6, 'Fuji', '1-2-6' );

SELECT
    *
FROM @Data AS d
OUTER APPLY (

    SELECT STRING_AGG ( [Name], '-' ) AS Replaced FROM @Data WHERE ID IN (
        SELECT CAST ( [value] AS INT ) FROM STRING_SPLIT ( d.To_Replace, '-' )
    )

) List
ORDER BY ID;

返回

+----+--------+------------+----------------------+
| ID |  Name  | To_Replace |       Replaced       |
+----+--------+------------+----------------------+
|  1 | Fruits | 1          | Fruits               |
|  2 | Apple  | 1-2        | Fruits-Apple         |
|  3 | Citrus | 1-3        | Fruits-Citrus        |
|  4 | Orange | 1-3-4      | Fruits-Citrus-Orange |
|  5 | Empire | 1-2-5      | Fruits-Apple-Empire  |
|  6 | Fuji   | 1-2-6      | Fruits-Apple-Fuji    |
+----+--------+------------+----------------------+

更新

确保在聚合名称时保持id列表顺序。

DECLARE @Data TABLE ( ID INT, [Name] VARCHAR(10), To_Replace VARCHAR(10) );
INSERT INTO @Data ( ID, [Name], To_Replace ) VALUES
    ( 1, 'Fruits', '1' ),
    ( 2, 'Apple', '1-2' ),
    ( 3, 'Citrus', '1-3' ),
    ( 4, 'Orange', '1-3-4' ),
    ( 5, 'Empire', '1-2-5' ),
    ( 6, 'Fuji', '1-2-6' ),
    ( 7, 'Test', '6-2-7' );

SELECT
    *
FROM @Data AS d
OUTER APPLY (

    SELECT STRING_AGG ( [Name], '-' ) AS Replaced FROM (
        
        SELECT TOP 100 PERCENT
            Names.[Name]
        FROM ( SELECT CAST ( '<ids><id>' + REPLACE ( d.To_Replace, '-', '</id><id>' ) + '</id></ids>' AS XML ) AS id_list ) AS xIds
        CROSS APPLY (
            SELECT 
                x.f.value('.', 'INT' ) AS name_id, 
                ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS row_id
            FROM xIds.id_list.nodes('//ids/id') x(f)
        ) AS ids
        INNER JOIN @Data AS Names ON Names.ID = ids.name_id
        ORDER BY row_id

    ) AS x

) List
ORDER BY ID;

返回

+----+--------+------------+----------------------+
| ID |  Name  | To_Replace |       Replaced       |
+----+--------+------------+----------------------+
|  1 | Fruits | 1          | Fruits               |
|  2 | Apple  | 1-2        | Fruits-Apple         |
|  3 | Citrus | 1-3        | Fruits-Citrus        |
|  4 | Orange | 1-3-4      | Fruits-Citrus-Orange |
|  5 | Empire | 1-2-5      | Fruits-Apple-Empire  |
|  6 | Fuji   | 1-2-6      | Fruits-Apple-Fuji    |
|  7 | Test   | 6-2-7      | Fuji-Apple-Test      |
+----+--------+------------+----------------------+

我确信这里可以进行优化，但是这个解决方案似乎可以保证列表顺序得到保持。

Answer 2

虽然您所要求的在技术上是可行的(可能使用递归查询或计数)，但我将采取不同的立场，建议您修复数据模型。

不应将多个值作为分隔列表存储在单个数据库列中。这违背了关系数据库的目的，使简单的事情变得不必要的复杂和低效。

相反，您应该有一个单独的表来存储这些数据，每个表都会在一个单独的行中替换id，并且可能会有一个列来指示列表中每个元素的顺序。

对于示例数据，如下所示：

id    replace_id    seq
1     1             1
2     1             1
2     2             2
3     1             1
3     3             2
4     1             1
4     3             2
4     4             3
5     1             1
5     2             2
5     5             3
6     1             1
6     2             2
6     6             3

现在，您可以通过连接、子查询或横向连接有效地生成预期的结果。假设您的表名为mytable，并且映射表为mymapping，则横向连接解决方案如下：

select t.*, r.*     
from mytable t
outer apply (
    select string_agg(t1.name) within group(order by m.seq) replaced
    from mymapping m
    inner join mytable t1 on t1.id = m.replace_id
    where m.id = t.id
) x