Quick suggestion as this isn't as complex as it seems at first, import the file's content into a staging table, one line per row and group/parse it from there.
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_LINE_IMPORT') IS NOT NULL DROP TABLE dbo.TBL_LINE_IMPORT;
CREATE TABLE dbo.TBL_LINE_IMPORT
(
LIMP_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_LINE_IMPORT_LIMP_ID PRIMARY KEY CLUSTERED
,LIMP_LINE VARCHAR(250) NOT NULL
);
/* The file content imported line by line */
INSERT INTO dbo.TBL_LINE_IMPORT (LIMP_LINE)
VALUES
('ABC 8585')
,('TRANID ')
,(' A4145')
,('CCA ')
,(' 96')
,('FVA ')
,(' 78')
,('ABO 8500')
,('ABD 8585')
,('TRANID ')
,(' A4146')
,('CCA ')
,(' 90')
,('FVA ')
,(' 71')
,('ABO 8110');
;WITH BASE_GROUP AS
(
SELECT
LI.LIMP_ID
FROM dbo.TBL_LINE_IMPORT LI
WHERE LTRIM(RTRIM(LI.LIMP_LINE)) = 'TRANID'
UNION ALL
SELECT MAX(LI.LIMP_ID) + 1 AS LIMP_ID
FROM dbo.TBL_LINE_IMPORT LI
)
,NUMBERED_GROUP AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY (SELECT NULL)
) AS BG_RID
,BG.LIMP_ID
FROM BASE_GROUP BG
)
,PARSE_GROUP AS
(
SELECT
NG.BG_RID
,NG.LIMP_ID AS START_ID
,LG.LIMP_ID - 1 AS END_ID
FROM NUMBERED_GROUP NG
INNER JOIN NUMBERED_GROUP LG
ON NG.BG_RID = LG.BG_RID - 1
)
,COMBINED_SET AS
(
SELECT
LI.LIMP_ID
,PG.BG_RID
,LTRIM(RTRIM(LI.LIMP_LINE)) AS LI_LINE
,LTRIM(RTRIM(L2.LIMP_LINE)) AS L2_LINE
FROM dbo.TBL_LINE_IMPORT LI
LEFT OUTER JOIN dbo.TBL_LINE_IMPORT L2
ON LI.LIMP_ID = (L2.LIMP_ID - 1)
CROSS APPLY PARSE_GROUP PG
WHERE LI.LIMP_ID BETWEEN PG.START_ID AND PG.END_ID
)
,FINAL_SET AS
(
SELECT
CASE
WHEN CS.LI_LINE = 'CCA' THEN CS.LI_LINE
WHEN CS.LI_LINE = 'FVA' THEN CS.LI_LINE
END AS fieldName
,CASE
WHEN CS.LI_LINE = 'CCA' THEN CS.L2_LINE
WHEN CS.LI_LINE = 'FVA' THEN CS.L2_LINE
END AS Fieldvalue
,MAX(CASE WHEN CS.LI_LINE = 'TRANID' THEN CS.L2_LINE END) OVER (PARTITION BY CS.BG_RID) AS TranID
FROM COMBINED_SET CS
)
SELECT
FS.fieldName
,FS.Fieldvalue
,FS.TranID
FROM FINAL_SET FS
WHERE FS.fieldName IS NOT NULL;
Results
fieldName Fieldvalue TranID
---------- ----------- -------
CCA 96 A4145
FVA 78 A4145
CCA 90 A4146
FVA 71 A4146