UNPIVOT in SELECT Statements - SQL Server to Databricks Migration

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.