In SQL Server you can use the UNPIVOT clause to rotate (unpivot) columns into rows. Databricks also supports UNPIVOT clause.
Consider a sample table:
CREATE TABLE colors ( yellow INT, blue INT, green INT, red INT, orange INT ); INSERT INTO colors VALUES (1, 2, 3, 4, 5);
Now let's run the following query with UNPIVOT:
SQL Server and Databricks:
Transform column names (!) “yellow”, “blue”, “green”, “red” and “orange” into row values of column “color” and transform their corresponding values to column “quantity”.
SELECT color, quantity FROM colors UNPIVOT ( quantity FOR color IN (yellow, blue, green, red, orange) ) AS colors_unpivoted;
Result:
color | quantity |
yellow | 1 |
blue | 2 |
green | 3 |
red | 4 |
orange | 5 |
For more information, see SQL Server to Databricks Migration.