Search code examples
sqlsql-servert-sql

Transform result of a table via subquery

I have this table in a header\details format . All rows that have Rw_Type =D are headers and the immediately following lines are the details of that line Rw_Type =M of that line until the next Rw_Type =D which represents a new Header.

There is always a single header with multiple rows representing the details

  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 ','*'  );

My goal is transform thi stable in this way:

enter image description here

I tried to proceed via subquery, but I wasn't successful. Any suggestions? Always grateful for any help

 select e.Serial, e.Rw,(
    select min(e1.Rw) from 
        #tbl e1 
        where e1.Serial= e.Serialand 
        e1.Type_Row = 'D' and 
        e1.Rw> e.Rw 
     ) AS c
    
    from #Tbl e 
    where e.Type_Row = 'M'
Solution

  • This is a gaps and islands problem that can be addressed by employing a running total to allocate a unique identifier to each group of successive rows that have Rw_Type = D following by rows with Rw_Type = M :

    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';

    Demo here