SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE CMT (
  id INT,
  ord_type char(1),
  ord_no char(8),
  line_sql_no smallint,
  cmt_sql_no smallint,
  cmt nvarchar(4000)
);

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (1,'O','186349',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (2,'O','186349',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (3,'O','186349',0,3,'Comment 3');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (4,'O','186350',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (5,'O','186350',0,2,'Comment 2');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (6,'O','186351',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (7,'O','186351',0,2,'Comment 2');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (8,'O','186352',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (9,'O','186352',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (10,'O','186352',0,3,'Comment 3');
SELECT * FROM CMT

CREATE TABLE CUS (
  id INT,
  ord_type char(1),
  order_no nchar(10),
  status char(1),
  email nchar(50)
);

INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (1,'O','186349','4','Ack:email1@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (2,'O','186350','4','Inv:email2@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (3,'O','186351','4','Ack:email3@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (4,'O','186352','1','Inv:email4@email.com');

SELECT * FROM CUS

id	ord_type	ord_no	cmt_sql_no	cmt
1	O	186349	1	Comment 1
2	O	186349	2	Comment 2
3	O	186349	3	Comment 3
4	O	186350	1	Comment 1
5	O	186350	2	Comment 2
6	O	186351	1	Comment 1
7	O	186351	2	Comment 2
8	O	186352	1	Comment 1
9	O	186352	2	Comment 2
10	O	186352	3	Comment 3

id	ord_type	order_no	status	email
1	O	186349	4	Ack:email1@email.com
2	O	186350	4	Inv:email2@email.com
3	O	186351	4	Ack:email3@email.com
4	O	186352	1	Inv:email4@email.com