non-standard csv header expansion

athena_csv_header_pivot.sql

WITH
  HEADER_ROWS AS (
   SELECT
     col0 key
   , col1 value
   , "$path" file
   FROM
     "database"."orders"
   WHERE (NOT (col0 LIKE 'PID_%'))
) 
, DATA_ROWS AS (
   SELECT
     col0 ts
   , col1 value
   , "$path" file
   FROM
     "enovo5"."orders"
   WHERE (col0 LIKE 'PID_%')
) 
, HEADER AS (
   SELECT
     file
   , "map_agg"(key, value) header
   FROM
     HEADER_ROWS
   GROUP BY file
) 
, ALL_DATA AS (
   SELECT
   , header['customer'] customer
   , header['order_id'] order_id
   , data_row.product_id
   , data_row.product_name
   , data_row.price
   , data_row.comment
   FROM
     (HEADER
   INNER JOIN DATA_ROWS data_row ON (HEADER.file = data_row.FILE))
)
SELECT * FROM ALL_DATA