SQL CASE WHEN Statement – Obtaining data based on one column and splitting into different columns (e.g. Cost Value into Location Columns)
I come across a situation were I needed to write a SQL Query where one value needed to be split out into columns (filtered pretty much). In the below example (which should make it clearer), on Project Server, I want to select project costs for UK and project costs for France.
You could use a WHERE clause, however this will only be useful if you want one result, and are happy with the AND OR type conditions.
In my case, I wanted a few fields in a SSRS report to populate a table. So, to get the cost, I used the CASE WHEN statement. Country is a project level custom field by the way.
CASE WHEN Country = ‘UK’ THEN ProjectCost END AS [UK Cost],
CASE WHEN Country = ‘France’ THEN ProjectCost END AS [France Cost]
Hope that is helpful for somebody!