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.

Answer 1

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).