databricksdatabricks-sql

Dynamic Transpose of Rows to Columns in Databricks SQL

I just have started to work with Databricks SQL. Here I have a table that contains for each article a list of attributes and values following this structure:

Article Attribute   Value
A   Name    Bike S
A   Size    5
A   Price   4
A   Costs   3
A   Department  CR
B   Name    Bike M
B   Size    5
B   Price   4
C   Name    Bike L

As you can see not every attribute exists for each article. Now, using Databricks SQL, how is it possible to transpose the rows to columns to get this result? enter image description here

Solution 
  • SELECT *
FROM (
  SELECT
    Article,
    Attribute,
    Value
  FROM
    articles
) AS source_table
PIVOT (
  MAX(Value) FOR Attribute IN ('Name', 'Size', 'Price', 'Costs', 'Department')
) AS pivot_table
ORDER BY Article;