Pivot in Databricks SQL

Data Engineering

Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.

Hi Team,

I have a table that has a key column (column name) and value column (value of the column name). These values are generated dynamically and wanted to pivot the table.

Question 1: Is there a way that we can do this without specifying all the columns in the expression_list?

i.e.

select id, date, col1, col2, col3, col4
from table1
pivot (
 max(value) as a 
 for key in ('col1', 'col2', 'col3', 'col4') <-- how to do this without specifying each column
)
group by all

Question 2: When I tried specifying a column in the expression_list (same code above)

getting these results