I am struggling with SQL codes in the Amazon Athena environment to transform it into a wide file format. I want to keep one id per row and create corresponding columns for each date from cpt19300 to cpt19307. The selection criteria are to select the earliest date for each cpt code. The final file will be wide, and it will be 1 id per row.
The original file looks like below.
And I would like to have something like below
id datecpt19300 cpt19300 datecpt19301 cpt19301 ------- datecpt19307 cpt19307
a null null 8/18/2020 1 .............................................
Please advice.
Thank you!