Search code examples
sqlsql-serverazureunpivotcross-apply

Cross apply on columns on SQL server. Syntax error near )

I am trying to unpivot several columns, but I can't find the way of solving the syntax error.

It says incorrect syntax near ')'.

Here is the code:

SELECT dates, times, locations, events
FROM mytable
CROSS APPLY 
    (VALUES ('instance1', instance1),
            ('instance2', instance2),
            ('instance3', instance3),
            ('instance4', instance4)) as Items(locations, events)

Could it be because my SQL Server version does not support values properly and I need to store the values in a different table to refer them for cross apply?

enter image description here

Solution

  • Since using VALUES like that has issues in your Azure SQL Data Warehouse, switch to UNPIVOT

    SELECT dates, times, locations, events
FROM mytable t
UNPIVOT (events FOR [locations] IN ([instance1],[instance2],[instance3],[instance4])) AS unpvt;

    Test here