WITH RawData AS (
  SELECT
    iso2,
    country_name,
    ARRAY_AGG(chemical_name ORDER BY chemical_name) AS chemical_names,
    ARRAY_AGG(CAST(chemical_code AS STRING) ORDER BY chemical_name) AS chemical_codes,
    ARRAY_AGG(action ORDER BY chemical_name) AS actions,
    player_type_label,
    MAX(pic_circular_date) AS pic_circular_date
  FROM
    `fao-maps-review.fao_rotterdam_convention.vw_final_regulatory_actions`
  WHERE
    ("all" IN UNNEST(SPLIT(@chemical_code, ',')) OR CAST(chemical_code AS STRING) IN UNNEST(SPLIT(@chemical_code, ',')))
    AND ("all" IN UNNEST(SPLIT(@iso2, ',')) OR iso2 IN UNNEST(SPLIT(@iso2, ',')))
    AND (@action = "Banned and Severely Restricted" OR action=@action)
  GROUP BY
    iso2,
    country_name,
    player_type_label
)

SELECT
  iso2,
  country_name,
  (
    SELECT STRING_AGG(CONCAT('[', CAST(index + 1 AS STRING), '] ', chemical), ' - ')
    FROM UNNEST(chemical_names) AS chemical WITH OFFSET AS index
  ) AS chemical_name,
  (
    SELECT STRING_AGG(CONCAT('[', CAST(index + 1 AS STRING), '] ', act), ' - ')
    FROM UNNEST(actions) AS act WITH OFFSET AS index
  ) AS action,
  player_type_label,
  pic_circular_date
FROM RawData;