Databricks Community

Gilg · ‎09-18-2023

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

atrev66 · ‎01-08-2025

you have to group by just the id columns and put functions on the rest of the columns you want. See below for solution. Yeah, it's tedious.

select id, date, max(col1) as col1, max(col2) as col2, max(col3) as col3, max(col4) as 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 id, date