SQL - 将逗号分隔的字符串转换为单独的行后更新表

Question

样本数据

CREATE TABLE Testdata
(
 SomeID INT,
 OtherID INT,
 String VARCHAR(MAX)
)
INSERT Testdata SELECT 1,  9, '18,20,22'
INSERT Testdata SELECT 2,  8, '17,19'
INSERT Testdata SELECT 3,  7, '13,19,20'
INSERT Testdata SELECT 4,  6, ''
INSERT Testdata SELECT 9, 11, '1,2,3,4'

我用以下查询成功去掉了逗号。

Select A.SomeID
,B.*
From [filter].[dbo].[Testdata] A
Cross Apply (
Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From  (Select x = Cast('<x>' + replace((Select 
replace(A.String,',','§§Split§§')
as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) 
as A 

Cross Apply x.nodes('x') AS B(i)
) B

现在，我想知道如何使用新的数值更新已有的表格。 谢谢。

- Jay Soorjun

---

修复你的数据模型！不要将多个整数值存储在字符串中。使用联接/关联表。 - Gordon Linoff

@GordonLinoff：我认为这实际上就是这里的想法。问题只是如何从A到B。 - Jeroen Mostert

你考虑的是什么样的更新语义呢？你的新表比旧表多了几行。OtherID 应该怎么处理——在新行中重复吗？由于旧表和新表之间没有一对一的映射关系，所以更容易的方法是从查询中创建一个新表，删除旧表并将其重命名（或者如果无法物理删除表，则删除/截断/插入）。唯一不适用的情况是如果您有指向 Testdata 的外键，但那样您的问题就变得更加复杂了。 - Jeroen Mostert

@JeroenMostert 我可以省略OtherID并删除旧表，创建一个新表也不是问题。那么我应该如何创建一个带有更新值的新表呢？ - Jay Soorjun

最佳方式：先使用 CREATE TABLE 创建表，然后使用 INSERT NewTable(SomeID, NewString) SELECT <your whole query> 插入数据。更简单的方式是使用 SELECT [columns] INTO NewTable FROM ...，但这将创建一个没有索引或主键的表，因此您需要在之后添加这些内容。 - Jeroen Mostert

你的 SQL Server 版本是什么？ - Thailo

Answer 1

既然您在评论中提到要创建一个新表，那么使用您现有的查询很容易实现。

SQL Server 2016之前的版本

CREATE TABLE NewData
(
  SomeID INT,
  String VARCHAR(MAX)
)

INSERT INTO NewData
SELECT A.SomeID,
       B.*
FROM [dbo].[Testdata] A
Cross Apply (
Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From  (Select x = Cast('<x>' + replace((Select 
replace(A.String,',','§§Split§§')
as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) 
as A 
Cross Apply x.nodes('x') AS B(i)
) B

SELECT * FROM NewData

SQL Server 2016发布后

现在你可以使用STRING_SPLIT来消除你的大型SQL块。

注：Original Answer翻译成“最初的回答”。

CREATE TABLE NewData2
(
  SomeID INT,
  String VARCHAR(MAX)
)

INSERT INTO NewData2
SELECT A.SomeID,
       S.value
FROM [dbo].[Testdata] A
CROSS APPLY STRING_SPLIT (A.String, ',') S

SELECT * FROM NewData2

你不会有任何限制等问题，因此你需要像平常一样将它们放在CREATE TABLE中。

注意：你应该真正改变你的表定义，现在存储整数而不是varchar。

- Jamie Pollard

Answer 2

CREATE TABLE Testdata
    (
     SomeID INT,
     OtherID INT,
     String VARCHAR(MAX)
    )
    INSERT Testdata SELECT 1,  9, '18,20,22'
    INSERT Testdata SELECT 2,  8, '17,19'
    INSERT Testdata SELECT 3,  7, '13,19,20'
    INSERT Testdata SELECT 4,  6, ''
    INSERT Testdata SELECT 9, 11, '1,2,3,4'

    CREATE TABLE NewData
    (
      SomeID INT,
      otherID int,
      String VARCHAR(MAX)
    )

    INSERT INTO  NewData (SomeID,OtherID,String)


    Select A.SomeID,OtherID,
    B.* 
    From [dbo].[Testdata] A
    Cross Apply (
    Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select 
    replace(A.String,',','§§Split§§')
    as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) 
    as A 

    Cross Apply x.nodes('x') AS B(i)
    ) B


    Delete  from Testdata
    Insert Testdata (SomeID, OtherID , String)

    select SomeID, OtherID , String from NewData

- Anusha Subashini