Search code examples
azuredatabricksazure-databricks

Unpivot operation in databricks using SQL?

I have a table called tbl1 in azure data bricks and I want to perform simple unpivot operation using SQL. I am new to SQL and DataBricks. I followed online tutorial to perform unpivot operation. Based on that I came up with below syntax. But. i am getting this You have an error in your SQL syntax; it seems the error is around: 'unpivot( height for details IN (ucol1, ucol2, ucol3))' error continuously.

SQL Syntax for unpivot operation.

%sql

Select date_format(X.Date,'dd-MMM')Datee
      ,X.width
      ,X.height
      ,X.details
      ,X.col1  
From
(
Select 
Datee,width,B.details,col1,height, from tbl1 A,
Unpivot
(
height 
for details IN (
       ucol1, ucol2, ucol3
)) B
GROUP BY Datee,width,B.Details,height,col1

)X

Is there anything wrong with the above SQL syntax? Any hint would be appreciable. Please let me know if you need any further details.

Solution

  • You can use stack in Spark SQL to unpivot, eg

    SELECT
    DATE_FORMAT( Date, 'dd-MMM') x,
    STACK( 3, ucol1, ucol2, ucol3 )
FROM tbl1;

    It would be helpful if you provided some simple sample data and expected results as it's not 100% clear what you need as your query does not work in any language.