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.

SELECT
ProjectName,
CASE WHEN Country = ‘UK’ THEN ProjectCost END AS [UK Cost],
CASE WHEN Country = ‘France’ THEN ProjectCost END AS [France Cost]

FROM
MSP_EpmProject_UserView

Hope that is helpful for somebody!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s