Pivot snowflake sql table

Question

I have an sql exercice on snowflake on which i have tried but not getting the expected result

Table A

req_id|N|id_text_a|text_a|id_text_b|text_b|
123|1|11|'aaaa'|20|'bbbb'|
123|1|2|'dddsa'|4|'cccc'|
123|1|50|'ffff'|111|'nnnn'|
456|44|231|'azerty'|426|'uiop'|
456|44|117|'fghjkl'|128|'xcvbn'|

the output should be like

req_id|N|id|id_text|text|
123|1|1|2|'dddsa'|
123|1|2|4|'cccc'|
123|1|3|11|'aaaa'|
123|1|4|20|'bbbb'|
123|1|5|50|'ffff'|
123|1|6|111|'nnnn'|
456|44|1|117|'fghjkl'|
456|44|2|128|'xcvbn'|
456|44|3|231|'azerty'|
456|44|4|426|'uiop'|

Any idea please ?

Answer 1

Assuming your source data table is called table_a :

    with 
    ua as 
       (select 
         req_id,
         N,
         id_text_a id_text,
         text_a text
       from table_a
       union all
       select 
         req_id,
         N,
         id_text_b id_text,
         text_b text
       from table_a
)
    
    select 
      req_id,
      N,
      row_number() over (partition by N order by id_text) AS id,
      id_text,
      text
    from ua
    order by 1,2,3
    ;