Pivot in Databricks SQL

[Gilg]

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

Wanted to collapse the result so that it will only be one row.

Cheers,

G

[Kaniz_Fatma · Accepted solution]

Hi @Gilg, In Databricks, performing a pivot operation is impossible without specifying all the columns in the expression list. This is because the pivot operation needs to know exactly which columns to pivot on. However, you can dynamically generate the column names if you can retrieve them from your data.

To collapse the result into one row, you can use the first function in Spark SQL.