T8EetN8q

1DROP TABLE IF EXISTS dbo.sampleData;
2DROP TABLE IF EXISTS dbo.lookupCPT;
3GO
4CREATE TABLE sampleData (
5    numRow bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_numRow PRIMARY KEY,
6    sDelimQty varchar(MAX) NULL,
7    sDelimCPT varchar(MAX) NULL
8    )
9GO
10CREATE TABLE lookupCPT (
11    numRow bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_numRowCPT PRIMARY KEY,
12    sCPTCode varchar(10) NULL,
13    decCPTRate decimal(16,2) NULL
14)
15
16INSERT [dbo].[lookupCPT] ([numRow], [sCPTCode], [decCPTRate]) VALUES (1, N'123', CAST(4.00 AS Decimal(16, 2)))
17GO
18INSERT [dbo].[lookupCPT] ([numRow], [sCPTCode], [decCPTRate]) VALUES (2, N'456', CAST(5.00 AS Decimal(16, 2)))
19GO
20INSERT [dbo].[lookupCPT] ([numRow], [sCPTCode], [decCPTRate]) VALUES (3, N'789', CAST(7.00 AS Decimal(16, 2)))
21GO
22INSERT [dbo].[sampleData] ([numRow], [sDelimQty], [sDelimCPT]) VALUES (1, N'1;2', N'123;789')
23GO
24INSERT [dbo].[sampleData] ([numRow], [sDelimQty], [sDelimCPT]) VALUES (2, N'3', N'456')
25GO
26	
27WITH Qty_CTE (numRowQ, Qty) AS
28(
29SELECT numRow, value
30FROM sampleData
31    CROSS APPLY STRING_SPLIT(sDelimQty, ';')
32),
33CPT_CTE (numRowC, CPT) AS
34(
35SELECT numRow, value
36FROM sampleData
37    CROSS APPLY STRING_SPLIT(sDelimCPT, ';')
38)
39SELECT * FROM sampleData 
40JOIN CPT_CTE c on c.numRowC = sampleData.numRow
41JOIN Qty_CTE q on q.numRowQ = sampleData.numRow