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

Question

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.

Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Charlieface, Dec 29 '21 at 22:03

score 3 · Accepted Answer · answered Dec 29 '21 at 17:48

3

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,',')

answered Dec 29 '21 at 17:48

Stu

30,392
6
14
33

1

Note that you must be using SQL Server 2016+ and the database itself needs to have a compatibility level of 130 or higher (SQL Server 2016). This should not be a common problem today, but if you do get an error that no function or procedure named STRING_SPLIT exists, this is why. – Bacon Bits Dec 29 '21 at 17:58
1

Thank you very much, it works. :-) – Denis Navračič Dec 29 '21 at 18:17
@Austin Yeah, I have some instances that remain stuck on 110 and can't use this (or 2017's STRING_AGG). Still, it really *should not* be a problem... if not for vendor laziness. It'll be another decade before I can reliably use the new ordinal column for STRING_SPLIT, too! – Bacon Bits Dec 29 '21 at 18:31
@BaconBits See https://stackoverflow.com/a/69928018/14868997, you can still use it if you want – Charlieface Dec 29 '21 at 22:03

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

1 Answers1