Using stored procdeure in Snowflake I am trying to change the columns order for a part of the data table.
Here is my table
column1 column2 column3 column4 column5
---------------------------------------------
Year Country Name City
2020 US Briand NY
2021 US John LA
2021 UK Mark London
City Name Year Country Age
Paris Jacques 2017 FR 55
Madrid Juan 2015 ES 25
Dublin Steven 2018 IE 37
So there is 5 differents structures in the same table.
All 5 structures doesn’t have the same number of columns but the column name can be match and missing column can be fill with null values. Moreover there is a blank row between each different data structure.
The output should be :
Year Country City Name Age
-------------------------------------------------
2020 US NY Briand Null
2021 US LA John Null
2021 UK London Mark Null
2017 FR Paris Jacques 55
2015 ES Madrid Juan 25
2018 IE Dublin Steven 37
So I thought that the only way is to do a stored procedure but I have no idea how it works and if it is possible to resolve my problem this way.
CREATE OR REPLACE PROCEDURE proc_columns_matching()
returns string
language javascript
as
$$
var sql = "select * from countries_pp";
var statement1 = snowflake.createStatement( {sqlText: pp} );
var result_set1 = statement1.execute();
while(result_set1.next() != ''){
var column1= result_set1.getColumnValue(1)
}
return column1;
$$
;
CALL proc_smart_impulse();
So I tried to identify the blank cell to split the table but I am stuck here.