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