PRESTO SQL conversion into Databricks SQL

Question

I am converting PRESTO sql to databricks sql. Would you please help me converting the following Subquery.

PSF_PIVOT
AS (SELECT A.PATIENT_ID, A.REPORT_ID, A.VISIT_DATE, A.DISEASE_GROUP, 
            MAP_AGG(A.NAME, A.VALUE) AS KV
    FROM PSF_BEST A
    GROUP BY A.PATIENT_ID, A.REPORT_ID, A.VISIT_DATE, A.DISEASE_GROUP
   )

I am having trouble converting MAP_AGG part above.

Thanks in Advance!

I tried using MAP instead of MAP_AGG function but ended the following error:

[COLUMN_NOT_IN_GROUP_BY_CLAUSE] The expression "NAME" is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in `first()` (or `first_value()`) if you don't care which value you get.

There is equivalent PIVOT method for achieving this. But I am not much familiar to this function in Databricks.

Answer 1

map is a scalar function and can't be used as aggregate one. Try using map_from_arrays over aggregated arrays:

select ...,
    map_from_arrays(array_agg(A.NAME), array_agg(A.VALUE)) AS KV
from ...
group by