如何在SQL中将01,02,03这样的列分隔为行

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

每一周都将在一行中——对于代码和客户，必须打破导入表中的周数记录用逗号分隔的周数。

这意味着一周，一个客户和一个代码将在一行中。

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;

我试过这个。。。但这是不对的。

Answer 1

看起来您想为周的每个不同值插入相同的行，您可以为此使用string_split：

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