SQL Server 2022

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

CREATE TABLE #Tbl
  ( 
    Serial VARCHAR(100),
    Rw INT ,
    Rw_Type VARCHAR(1),
    Decrip_Article  VARCHAR(max),
    Is_Article VARCHAR(1)
  ) 

INSERT INTO #Tbl ( Serial, Rw, Rw_Type, Decrip_Article, Is_Article)
VALUES( '0000265954',   '30',   'D',    'ORD.2023217BaltiniBAL7700070861','.' ),                  
('0000265954',  '40',   'M',    '36200 - JEANS size : 4 color :0224','*'  ),                  
('0000265954',  '60',   'D',    'ORD.2023217BaltiniBAL7700070898','.'   ),                 
('0000265954',  '70',   'M',    'AAUBT0002FA01 - BELT size : 01 color :GR','*'   ),         
('0000265954',  '70',   'M',    'AAUBT0002FA01 - BELT size : 05 color :RR','*'  ),             
('0000265954',  '90',   'D' ,'ORD.2023217BaltiniBAL7700070887 ' ,   '.'    ),               
('0000265954',  '100',  'M' ,'PERGSA049CR0010 - SHOULDERBAG size : 01 ','*'  ), 
('0000265954',  '100',  'M' ,'PERGSA049CR0010 - SHOULDERBAG size : 02 ','*' ),  
('0000265954',  '100',  'M' ,'PERGSA049CR0010 - SHOULDERBAG size : 03 ','*'  );
9 rows affected

with cte as (
  SELECT *, sum(case when Rw_Type = 'D' then 1 else 0 end) over (partition by Serial order by Rw) as grp
  FROM #Tbl
)
select *
from cte c1
inner join cte c2 on c1.Serial = c2.Serial and c1.grp = c2.grp and c2.Rw_Type = 'M'
where c1.Rw_Type = 'D'


Serial
Rw
Rw_Type
Decrip_Article
Is_Article
grp
Serial
Rw
Rw_Type
Decrip_Article
Is_Article
grp


0000265954
30
D
ORD.2023217BaltiniBAL7700070861
.
1
0000265954
40
M
36200 - JEANS size : 4 color :0224
*
1

0000265954
60
D
ORD.2023217BaltiniBAL7700070898
.
2
0000265954
70
M
AAUBT0002FA01 - BELT size : 01 color :GR
*
2

0000265954
60
D
ORD.2023217BaltiniBAL7700070898
.
2
0000265954
70
M
AAUBT0002FA01 - BELT size : 05 color :RR
*
2

0000265954
90
D
ORD.2023217BaltiniBAL7700070887
.
3
0000265954
100
M
PERGSA049CR0010 - SHOULDERBAG size : 01
*
3

0000265954
90
D
ORD.2023217BaltiniBAL7700070887
.
3
0000265954
100
M
PERGSA049CR0010 - SHOULDERBAG size : 02
*
3

0000265954
90
D
ORD.2023217BaltiniBAL7700070887
.
3
0000265954
100
M
PERGSA049CR0010 - SHOULDERBAG size : 03
*
3


WITH cte AS (
  SELECT *, SUM(CASE WHEN Rw_Type = 'D' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Serial ORDER BY Rw ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as grp
  FROM #Tbl
)
SELECT c1.Serial, c1.Rw as Header_row, c1.Rw_Type as Header_Type_row, c1.Decrip_Article as Header, 
       c2.Rw as Details_article_row, c2.Rw_Type as Details_article_type_row, c2.Decrip_Article as Details_article
FROM cte c1
LEFT JOIN cte c2 on c1.Serial = c2.Serial and c1.grp = c2.grp and c2.Rw_Type = 'M'
WHERE c1.Rw_Type = 'D'


Serial
Header_row
Header_Type_row
Header
Details_article_row
Details_article_type_row
Details_article


0000265954
30
D
ORD.2023217BaltiniBAL7700070861
40
M
36200 - JEANS size : 4 color :0224

0000265954
60
D
ORD.2023217BaltiniBAL7700070898
70
M
AAUBT0002FA01 - BELT size : 01 color :GR

0000265954
60
D
ORD.2023217BaltiniBAL7700070898
70
M
AAUBT0002FA01 - BELT size : 05 color :RR

0000265954
90
D
ORD.2023217BaltiniBAL7700070887
100
M
PERGSA049CR0010 - SHOULDERBAG size : 01

0000265954
90
D
ORD.2023217BaltiniBAL7700070887
100
M
PERGSA049CR0010 - SHOULDERBAG size : 02

0000265954
90
D
ORD.2023217BaltiniBAL7700070887
100
M
PERGSA049CR0010 - SHOULDERBAG size : 03

Serial	Rw	Rw_Type	Decrip_Article	Is_Article	grp	Serial	Rw	Rw_Type	Decrip_Article	Is_Article	grp
0000265954	30	D	ORD.2023217BaltiniBAL7700070861	.	1	0000265954	40	M	36200 - JEANS size : 4 color :0224	*	1
0000265954	60	D	ORD.2023217BaltiniBAL7700070898	.	2	0000265954	70	M	AAUBT0002FA01 - BELT size : 01 color :GR	*	2
0000265954	60	D	ORD.2023217BaltiniBAL7700070898	.	2	0000265954	70	M	AAUBT0002FA01 - BELT size : 05 color :RR	*	2
0000265954	90	D	ORD.2023217BaltiniBAL7700070887	.	3	0000265954	100	M	PERGSA049CR0010 - SHOULDERBAG size : 01	*	3
0000265954	90	D	ORD.2023217BaltiniBAL7700070887	.	3	0000265954	100	M	PERGSA049CR0010 - SHOULDERBAG size : 02	*	3
0000265954	90	D	ORD.2023217BaltiniBAL7700070887	.	3	0000265954	100	M	PERGSA049CR0010 - SHOULDERBAG size : 03	*	3