topic Re: Pivot in Databricks SQL in Data Engineering

Pivot in Databricks SQL

Gilg — Tue, 19 Sep 2023 01:52:07 GMT

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,

Re: Pivot in Databricks SQL

atrev66 — Wed, 08 Jan 2025 22:33:19 GMT

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

Re: Pivot in Databricks SQL

NSonam — Thu, 09 Jan 2025 02:16:40 GMT

PySpark can help to list the available columns .Please find the demo snippets as below

Image 1.

Image 2