Error when using UNPIVOT in SQL Server

Question

I have the following query:

    SELECT STDEV(Value) as Value, TimeOfTest as Date
    FROM myTable
    unpivot
    (
        value
        for col in (WS1, WS2, WS3, WS4, WS5, WS6, WS7, WS8, WS9, WS10, WS11, 
                    WS12, WS13, WS14, WS15, WS16, WS17, WS18, WS19, WS20)
    ) un
    GROUP BY TimeOfTest
    ORDER BY TimeOfTest DESC

but I get an SQL server 2008 error: Incorrect syntax near the keyword 'FOR'

Would anyone know the reason why? The syntax looks correct.

for unpivot no need to add single quotes as we will mention the column names in the 'in' clause available at unpivot clause — Smart003, Jun 06 '18 at 12:19

score 3 · Accepted Answer · answered Jun 06 '18 at 12:08

3

Just use APPLY.

SELECT t.TimeOfTest, STDEV(ws) as Value
FROM myTable t CROSS APPLY(
     (VALUES (WS1), (WS2), . . . (WS20)) v(ws)
GROUP BY t.TimeOfTest
ORDER BY t.TimeOfTest DESC;

APPLY implements something called a "lateral join". This is a very powerful (and ANSI-standard) construct, that can be used for many things beyond unpivoting. On the other hand, unpivot is very specific syntax that is used for only one purpose . . . and as you have found, may not work well other constructs such as GROUP BY (I think you could resolve your issue by using a subquery).

answered Jun 06 '18 at 12:08

Gordon Linoff

1,242,037
58
646
786

don't ws1 etc. need quotes? – George Menoutis Jun 06 '18 at 12:11
This worked great for me! @GeorgeMenoutis the values do not need to be in quotes. – N. Dubois Jun 06 '18 at 12:18
1

well, nice! unpivoting with cross apply is so much easier, more intuitional, and configurable than the unpivot operator. – George Menoutis Jun 06 '18 at 12:22

Error when using UNPIVOT in SQL Server

1 Answers1