How to separate column like 01,02,03 to rows in SQL?-CodePudding

CREATE TABLE Tabx_Test_Import
(
    ID        INT NOT NULL IDENTITY(1, 1),
    Week      NVARCHAR(2000),
    Code      NVARCHAR(20),
    Amount    NUMERIC(19, 6),
    Name      NVARCHAR(50),
    Last_Name NVARCHAR(50),
    RC        NVARCHAR(11)
) ON [PRIMARY]

CREATE TABLE Tabx_Test_Inz
(
    ID     INT NOT NULL IDENTITY(1, 1),
    Week   INT NOT NULL,
    RC     NVARCHAR(10),
    Code   NVARCHAR(20),
    Amount NUMERIC(19, 6),
) ON [PRIMARY]

INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'01,02,03', N'012016A15', 11.50, N'Juraj', N'Novotný', N'050671/8652')

INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'04,05,08', N'012016G45', 22.30, N'Peter', N'Pýchly', N'030888/3553')

INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'10,11,12,13', N'012016A18', 8.70, N'Juraj', N'Novotný', N'050671/8652')

INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'04,05,06', N'012016T66', 12.12, N'Peter', N'Pýchly', N'030888/3553')

INSERT INTO Tabx_Test_Import (Week, Code, Amount, Name, Last_Name, RC)
VALUES (N'04', N'012016H11', 55.00, N'Peter', N'Pýchly', N'030888/3553')

Each week will be in one line - the record with weeks from the import table have to be broken for the code and the customer for so many lines how many weeks are separated by commas.

It means one week, one customer and one code will be in one line.

INSERT INTO Tabx_Test_Inz  (Week, RC, Code, Amount) 
    SELECT 
        SUBSTRING(Week,1,2), 
        REPLACE(RC,'/','') , Code, Amount
    FROM
        Tabx_Test_Import;

INSERT INTO Tabx_Test_Inz  (Week, RC, Code, Amount) 
    SELECT 
        SUBSTRING(Week,4,2), 
        REPLACE(RC,'/','') , Code, Amount
    FROM 
        Tabx_Test_Import;

INSERT INTO Tabx_Test_Inz  (Week, RC, Code, Amount) 
    SELECT 
        SUBSTRING(Week,7,2), 
        REPLACE(RC,'/','') , Code, Anount
    FROM 
        Tabx_Test_Import;

INSERT INTO Tabx_Test_Inz  (Week, RC, Code, Amount)
    SELECT
        SUBSTRING(Week,10,2),
        REPLACE(RC,'/','') , Code, Amount
    FROM 
        Tabx_Test_Import;

I've tried this... But it is not correct.

CodePudding user response：

It looks like you want to insert the same rows for each distinct value for week, you can use string_split for this purpose:

 insert into Tabx_Test_Inz (Week, RC, Code, Amount)
 select value, replace(RC,'/',''), code, amount
 from Tabx_Test_Import
 cross apply String_Split(week,',')