2

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

LukStorms
  • 28,916
  • 5
  • 31
  • 45
zhivab
  • 99
  • 11
  • what is instance1...4 column/variable from (the 2nd values object) [Demo](https://rextester.com/EVKB50243) Expected results would help clarify what you're trying to do. – xQbert Dec 10 '18 at 22:05
  • The syntax looks correct. Cross Apply goes back to 2005 and Values Constructor was introduced in 2008. What version are you running. Try select @@version – John Cappelletti Dec 10 '18 at 22:10
  • Without knowing your version it is hard to say if this is an issue with your version. What one are you running? – Sean Lange Dec 10 '18 at 22:11
  • Running on Microsoft SQL Server Management Studio v17.7 – zhivab Dec 10 '18 at 22:12
  • 1
    The version of SSMS is irrelevant, it is the version of the instance you are connected to that matters. select @@VERSION – Sean Lange Dec 10 '18 at 22:14
  • 1
    Microsoft Azure SQL Data Warehouse - 10.0.9999.0 Nov 15 2018 08:34:24 Copyright (c) Microsoft Corporation – zhivab Dec 10 '18 at 22:17
  • 5
    According to the documentation on [table valued constructors](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-2017) they are not available in Azure SQL Data Warehouse. – Sean Lange Dec 10 '18 at 22:21

2 Answers2

2

Pretty cool, I've never unpivoted that way. I always use the UNPIVOT command. But it does seem to work pretty well. Without knowing the structure of your mytable I don't know the problem, but I am guessing it doesn't have columns names instance1 through instance4?

Here's a self-contained working example:

select dates
      ,times
      ,locations
      ,events
from
(
    values
        ('20181225', 'noon', 'a', 'b', 'c', 'd')
       ,('20181226', 'midnight', 'e', 'f', 'g', 'h')
) mytable (dates, times, instance1, instance2, instance3, instance4)
cross apply
(
    values
        ('instance1', instance1)
       ,('instance2', instance2)
       ,('instance3', instance3)
       ,('instance4', instance4)
) as Items (locations, events);
Ed Callahan
  • 189
  • 6
  • Hi, I have added a picture at the top. I literally have copied your code and I find the same error – zhivab Dec 10 '18 at 22:16
  • You were right. A SQL version problem. Works on Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 2 2018. But not SQL Data Warehouse. – Ed Callahan Dec 10 '18 at 22:26
1

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

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • I tried this solution, but it does not keep the null values. Anyway to keep also the null values? – zhivab Dec 10 '18 at 22:48
  • @zhivab Yes, I got a workaround for that, but it ain't pretty. Basically replace the table by a sub-query : `... FROM (select dates, times, isnull(instance1,'') as instance1, isnull(instance2,'') as instance2, isnull(instance3,'') as instance3, isnull(instance4,'') as instance4 from mytable) t ...` – LukStorms Dec 10 '18 at 23:11