以逗号分隔字符串，并将其作为sql中的一行插入表中

Question

我的数据库中有下表，其中两列有逗号分隔的字符串。我想根据逗号分割列，并将字符串作为一行插入数据库中。以下是我的表格和插入声明：

CREATE TABLE [dbo].[TABLEA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DocNumber] [varchar](50) NULL,
[InternalDocNumber] [varchar](50) NULL,
[Date] [varchar](50) NULL,
[DocType] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[NameG] [varchar](max) NULL,
[NameGR] [varchar](max) NULL,
[NumberPages] [varchar](50) NULL)

以下是表中的插入语句：

INSERT INTO [dbo].[TABLEA]
([DocNumber]
,[InternalDocNumber]
,[Date]
,[DocType]
,[Description]
,[NameG]
,[NameGR]
,[NumberPages])
VALUES
(1
,1235
,'12/23/2020'
,3
,'this is a test'
,'test1, test2, test3'
,'test6, test4'
,1),
(2
,3456
,'12/24/2020'
,3
,'this is a test1'
,'test4, test5, test6'
,'test9, test4'
,2)
,
(6
,6789
,'12/24/2020'
,3
,'this is a test3'
,'test9'
,'test100, test15, test16'
,2)
GO

根据上表。我想创建一个新的表格，结果如下：

ID   DocNumber  InternalDocnumber  date       DocType Description     NameG                 NameGR          NumberPage
1     1              1235         12/23/2020   3     thisisaTest    test1                 test6             1
1     1               1235         12/23/2020   3     thisisaTest    test2                 test4             1
1     1               1235         12/23/2020   3     thisisaTest    test3                 NULL              1
2     2               3456         12/24/2020   3     thisisaTest1   test4                 test9             2
2     2               3456         12/24/2020   3     thisisaTest1   test5                 test4             2
2     2               3456         12/24/2020   3     thisisaTest1   test6                 NULL              2
3     6               6789         12/24/2020   3     thisisaTest3   test9                 test100           2
3     6               6789         12/24/2020   3     thisisaTest3   NULL                  test15            2
3     6               6789         12/24/2020   3     thisisaTest3   NULL                  test16            2

基本上，我希望出现在列NameG和NameGR中的逗号分隔字符串基于逗号进行拆分，然后作为新行插入到新表中。顺序非常重要，如果NameG列中有"Test1"，那么columnGR中应该有"Test6"。

如有任何帮助，我们将不胜感激。

Answer 1

这在SQL Server中是一个难题，因为strint_split()不提供数字，甚至不提供有保证的排序。因此，使用递归CTE:

with cte as (
select a.docnumber, convert(varchar(max), null) as gr, convert(varchar(max), null) as g,
convert(varchar(max), nameGR) as restGR, convert(varchar(max), nameG) as restG, 0 as lev
from tableA a
union all
select cte.docnumber,
left(restgr, charindex(',', restgr + ',') - 1) as gr,
left(restg, charindex(',', restg + ',') - 1) as g,
stuff(restgr, 1, charindex(',', restgr + ',') + 1, '') as restgr,
stuff(restg, 1, charindex(',', restg + ',') + 1, '') as restg,
lev + 1
from cte
where restgr > '' or restg > ''
)
select id, gr, g
from cte
where lev > 0;

这是一个数据库<>不停摆弄

这只显示了一个docnumber列。您可以添加其余列。我认为他们只是混淆了演示。

Answer 2

with gx as
(select [Id], [NameG], 
row_number() over (partition by [id] order by [Id]) as rNo
from (select [Id] ,ltrim(x.Value) as [NameG]
from tablea
cross apply string_split(nameG, ',') as x) g),
grx as (
select [Id], [NameGR], 
row_number() over (partition by [Id] order by [Id]) as rNo
from
(select [Id] ,ltrim(x.Value) as [NameGR]
from tablea
cross apply string_split(nameGR, ',') as x) gr),
names (Id, NameG, NameGR, r1, r2) as 
( select coalesce(gx.Id, grx.Id), gx.NameG, grx.NameGR, coalesce(gx.rNo, grx.rNo), coalesce(grx.rNo, gx.rNo) 
from gx
full join grx on gx.Id = grx.Id and gx.rNo = grx.rNo)
select  a.Id
,[DocNumber]
,[InternalDocNumber]
,[Date]
,[DocType]
,[Description]
,n.[NameG]
,n.[NameGR]
,[NumberPages]
from tableA a
inner join names n on a.Id = n.Id 
order by a.Id, r1, r2;

Answer 3

使用函数Call STRING_SPLIT

SELECT ID,value AS NameG
FROM  TABLEA
CROSS APPLY STRING_SPLIT(NameG, ',')

使用此函数，分隔的逗号值​​是分开的，但与ID 的关系

然后我们寻找与tablea 的id的关系

WITH DataSplit as
(
SELECT ID,value AS NameG
FROM  TABLEA
CROSS APPLY STRING_SPLIT(NameG, ',')
)
select TABLEA.Id,TABLEA.DocNumber,TABLEA.InternalDocNumber,TABLEA.Date,
TABLEA.DocType,TABLEA.Description, DataSplit.NameG
from TABLEA inner join DataSplit on TABLEA.id=DataSplit.id;

有关的详细信息，请参阅fiddler中的示例