convert table with text file data from header and detail rows to combined rows

Question

Hi,

I have fixed position text files loaded in to a table for later splitting into columns and loading in to an application. Works well but now a file layout has appeared (not in my responsibility) with invoice header as first line marked 'H', and invoice rows as preceding lines marked 'R', repeated for each invoice.

What is the Update SQL for placing the Header data after the Row data? (the deletion of header row is no problem, can be executed after update).

From:

Row FileContent
1 H Customer1 01/01/2023
2 R Product1 1000.00
3 R Product2 2000.00
4 H Customer2 01/01/2023
5 R Product1 1000.00
6 R Product3 3000.00
7 R Product4 4000.00

To:

Row FileContent
2 R Product1 1000.00 H Customer1 01/01/2023
3 R Product2 2000.00 H Customer1 01/01/2023
5 R Product1 1000.00 H Customer2 01/01/2023
6 R Product3 3000.00 H Customer2 01/01/2023
7 R Product4 4000.00 H Customer2 01/01/2023

CREATE TABLE [dbo].[FileImport](
	[Row] [int] NOT NULL,
	[FileContent] [varchar](1000) NOT NULL,
 CONSTRAINT [PK_FileImport] PRIMARY KEY CLUSTERED 
(
	[Row] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT [dbo].[FileImport] ([Row], [FileContent]) VALUES (1, N'H Customer1 01/01/2023')
INSERT [dbo].[FileImport] ([Row], [FileContent]) VALUES (2, N'R Product1 1000.00')
INSERT [dbo].[FileImport] ([Row], [FileContent]) VALUES (3, N'R Product2 2000.00')
INSERT [dbo].[FileImport] ([Row], [FileContent]) VALUES (4, N'H Customer2 01/01/2023')
INSERT [dbo].[FileImport] ([Row], [FileContent]) VALUES (5, N'R Product1 1000.00')
INSERT [dbo].[FileImport] ([Row], [FileContent]) VALUES (6, N'R Product3 3000.00')
INSERT [dbo].[FileImport] ([Row], [FileContent]) VALUES (7, N'R Product4 4000.00')

Answer 1

Hi @Jrmkjrm

Based on your needs, I wrote the following query.

;with T1 as(
  select * from [dbo].[FileImport] where left(FileContent,1) = 'R'
),T2 as(
  select * from [dbo].[FileImport] where left(FileContent,1) = 'H'
),T3 as(
  select A.Row as AR,A.FileContent as AF,B.Row as BR,B.FileContent as BF 
  from T1 as A cross join T2 as B where A.Row > B.Row
),T4 as(
  select *,row_number()over(partition by AR order by BR desc) as num from T3
),T5 as(
  select AR as Row,AF + ' ' + BF as FileContentNew from T4 where num = 1)
select * from T5;

Output:

Since the output you expect needs to delete two rows of the original table, a simple update statement is not easy to implement in one step. I suggest that you can store the query results in a new table.

Best regards,

Percy Tang