Concatenate multiple rows 1

[Thargy]

I am processing some DML produced by an automatic tool. Unfortunately, some of the data contained carriage return and line feed characters, which caused the data to be spread across several rows. I need to joing them back together, to make valid insert statements.

Sample data is:-

CREATE TABLE SQL_SERVER_AUDITLOGBTSTATS
(
  ROW_NUMBER  NUMBER,
  DML_STRING  VARCHAR2(4000 BYTE),
  TABLE_NAME  VARCHAR2(4000 BYTE)
);

INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (771, 'SET IDENTITY_INSERT AuditLogBTStats ON', 'AUDITLOGBTSTATS');
 
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (772, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (44, ''23'',''StartSearch( 23)'',''Product = ''''*''''', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (773, 'Suppliers = ''''*''''', 'SUPPLIERS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (774, 'Clinicians = ''''*''''', 'CLINICIANS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS  (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (775, 'Month =  0Year = 0', 'MONTH');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (776, 'Dates = 01/02/2005 to 28/02/2005', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (777, 'Pat Cat = ''''*''''', 'PAT CAT');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (778, 'Reasons = ''''*''''', 'REASONS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (779, 'Sorting on = SURNAME Order = A', 'SORTING ON');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (780, ''', CAST(0x0000962801273890 AS DateTime), ''Type mismatch'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (781, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (45, ''18'',''StartSearch( 18)'',''Month =  0Year = 0', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (782, 'Dates = 01/06/2005 to 30/06/2005', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (783, ''', CAST(0x0000968400ADD978 AS DateTime), ''Type mismatch'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (784, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (46, ''18'',''StartSearch( 18)'',''Month =  0Year = 0', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (785, 'Dates = 01/07/2005 to 31/07/2005', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (786, ''', CAST(0x000096A3009E7348 AS DateTime), ''Type mismatch'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (787, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (47, ''18'',''StartSearch( 18)'',''Month =  0Year = 0', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (788, 'Dates = 01/07/2005 to 31/07/2005', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (789, ''', CAST(0x000096A3009EADE0 AS DateTime), ''Type mismatch'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (790, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (48, ''22'',''clsGeneralExtract.GetStats()'',''Product = ''''*''''', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (791, 'Suppliers = ''''*''''', 'SUPPLIERS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (792, 'Clinicians = ''''*''''', 'CLINICIANS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (793, 'Month =  0Year = 0', 'MONTH');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (794, 'Dates = 01/10/2005 to 31/12/2005', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (795, 'Pat Cat = ''''*''''', 'PAT CAT');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (796, 'Reasons = ''''*''''', 'REASONS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (797, 'Sorting on = SURNAME Order = A', 'SORTING ON');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (798, ''', CAST(0x0000974A009ECEB0 AS DateTime), ''Permission denied'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (799, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (49, ''22'',''clsGeneralExtract.GetStats()'',''Product = ''''*''''', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (800, 'Suppliers = ''''*''''', 'SUPPLIERS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (801, 'Clinicians = ''''*''''', 'CLINICIANS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (802, 'Month =  0Year = 0', 'MONTH');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (803, 'Dates = 01/05/2006 to 31/05/2006', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (804, 'Pat Cat = ''''*''''', 'PAT CAT');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (805, 'Reasons = ''''*''''', 'REASONS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (806, 'Sorting on = SURNAME Order = A', 'SORTING ON');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (807, ''', CAST(0x000097D400F13218 AS DateTime), ''Permission denied'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (808, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (50, ''23'',''StartSearch( 23)'',''Month =  0Year = 0', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (809, 'Dates = 01/04/2005 to 31/03/2006', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (810, ''', CAST(0x0000980900FE4624 AS DateTime), ''Type mismatch'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (811, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (51, ''23'',''StartSearch( 23)'',''Month =  0Year = 0', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (812, 'Dates = 01/04/2005 to 31/03/2006', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (813, ''', CAST(0x0000980900FE649C AS DateTime), ''Type mismatch'')', NULL);
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (814, 'INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (52, ''17'',''StartSearch( 17)'',''Month =  0Year = 0', 'AUDITLOGBTSTATS');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (815, 'Dates = 00:00:00 to 00:00:00', 'DATES');
INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (816, ''', CAST(0x0000980900FF24CC AS DateTime), ''Type mismatch'')', NULL);

INSERT INTO SQL_SERVER_AUDITLOGBTSTATS (ROW_NUMBER, DML_STRING, TABLE_NAME)
 VALUES (943, 'SET IDENTITY_INSERT AuditLogBTStats OFF', 'AUDITLOGBTSTATS');
COMMIT;

W.R.T. the sample data,

SELECT ROW_NUMBER,DML_STRING 
  FROM SQL_SERVER_AUDITLOGBTSTATS
 WHERE ROW_NUMBER BETWEEN 781 AND 784;

yields

ROW_NUMBER DML_STRING
781 INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (45, '18','StartSearch( 18)','Month =  0Year = 0
782 Dates = 01/06/2005 to 30/06/2005
783 ', CAST(0x0000968400ADD978 AS DateTime), 'Type mismatch')
784 INSERT AuditLogBTStats (ErrorID, Query Type, Code Routine, Query Parameters, Error Date, Error Description) VALUES (46, '18','StartSearch( 18)','Month =  0Year = 0

I want to get the DML_STRING of 781
concatenated with CR/LF,
concatenated with the DML_STRING of 782
concatenated with CR/LF,
concatenated with the DML_STRING of 783.

Row 784 contains the word "INSERT" so it denotes the start of the next insert statement. It requires the above concatenation but with the content of rows 785 and 786.

Note that there are a variable number of rows involved. It just so happens that my example joins 3 rows, but this can vary, so the solution has to cater for an unknown number of rows.

Regards

T

 

[Dagon]

Fairly easy to do using PL/SQL but a bit more tricky with SQL. You should be able to use this sort of approach.

create table sql_text (line_no number, text varchar2(1000));

insert into sql_text VALUES (1, 'insert into table (a, b, c, x, y, z)');
insert into sql_text VALUES (2, 'values (1,2,3,');
insert into sql_text VALUES (3, '4,5,6)');
insert into sql_text VALUES (4, 'insert into table (x, y, z)');
insert into sql_text VALUES (5, 'values (4,5,6)');


CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);


CREATE or replace FUNCTION to_string (
                   nt_in        IN varchar2_ntt,
                   delimiter_in IN VARCHAR2 DEFAULT ','
                   ) RETURN VARCHAR2 IS
      v_idx PLS_INTEGER;
      v_str VARCHAR2(32767);
      v_dlm VARCHAR2(10);
   BEGIN
      v_idx := nt_in.FIRST;
     WHILE v_idx IS NOT NULL LOOP
         v_str := v_str || v_dlm || nt_in(v_idx);
         v_dlm := delimiter_in;
         v_idx := nt_in.NEXT(v_idx);
      END LOOP;
      RETURN v_str;
   END to_string;
/

select to_string(cast(collect(b.text) as varchar2_ntt), chr(12))
from 
(select line_no, nvl(lead(line_no) over (order by line_no), 10000000) as next_line_no
from sql_text
where text like 'insert%') h,
sql_text b
where 
b.line_no between h.line_no and h.next_line_no-1
group by h.line_no

 

[Thargy]

Thanks Dagon.

Naturally, I've just been assigned a priority task, so I'll have a look at this later. I had a mental block on this one - as you so rightly say, it's relatively easy.

Regards

T