It is frustrating that I need to pull the data from SQL server using complex queries and then format it in Excel to ”prettify” the data. Thankfully, I can use the Microsoft query in Excel to get the data from SQL server and create a table or a PivotTable. Today, I encountered a problem with the case statement.
Although my syntax was right, MS Query kept throwing exception about the CASE statement. I read some discussions on-line, and some people had the wrong syntax, such as including the alias of the case statement in the group by statement. I did not have that problem. Finally, I found the solution: It was the order by clause. I was using the alias of the case statement in the order by clause. I removed it and the query ran just fine.