CODEWITHSUNDEEP
sqlsql-server
cool
cool

Reputation: 143

Splitting a string on comma and inserting it in a table as a row in sql

I have the following table in my database where two of the columns has comma separated string. I want to split the columns based on comma and insert the string in the database as a row. Below is my table and insert statements:

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)

Below are the insert statements in the table:

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

From the above table. I want to create a new table that have a result like below:

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

Basically, I want the comma delimited string that is present in column NameG and NameGR to be splitted on comma based and then insert in a new table as anew row. The order is very important, if there is "Test1" in NameG column then here should be "Test6" in columnGR.

Any help with this will be highly appreciated.

Upvotes: 0

Views: 412

Answers (3)

Barney CBZ
Barney CBZ

Reputation: 121

use the function Call STRING_SPLIT

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

with this function the separated comma values ​​are separated but with the relation of the ID

then we look for the relationship with the id of tablea

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;

see the example in fiddler for more detail

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a pain in SQL Server, because strint_split() doesn't provide a number or even a guaranteed ordering. So instead, use a recursive 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;

Here is a db<>fiddle.

This only shows the one docnumber column. You can add the rest of the columns. I think they just confuse the presentation.

Upvotes: 1

Related Questions