CODEWITHSUNDEEP
sql-server
user2119980
user2119980

Reputation: 509

Case When for Specific Criteria

I have a query that lists employees ratings throughout the years, but the years are all listed in one column. I am trying to get the meeting years to have their own columns with the score, and if they do not have a score for it to be null.

THe Code so far:

SELECT Employee_ID, Meeting_Year, Manager_Readiness_Rating
FROM  dbo.v_sc17_TMS_Data_Career_Meeting_Rating
GROUP BY Employee_ID, Meeting_Year, Manager_Readiness_Rating

So if the meeting_year is 2012 I want all 2012 Manager_Readiness_Rating listed in the column for the employees. Here is some examples

ID    Year      Rating
1     2011  11 
2     2012  10 
3     2010  09
4     2010  03  
4     2011  03

I would like it to look like

ID  2010   2011  2012
1    NULL   11    NULL
2    NULL   NULL  1
3    09    NULL    NULL
4    03    03    NULL

Upvotes: 0

Views: 29

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26343

This is flagged as MySQL, but the dbo makes me think it's SQL Server. This query should work for either one:

SELECT
  Employee_ID AS ID,
  MAX(CASE WHEN Year = 2010 THEN Rating END) AS R2010,
  MAX(CASE WHEN Year = 2011 THEN Rating END) AS R2011,
  MAX(CASE WHEN Year = 2012 THEN Rating END) AS R2012
FROM dbo.v_sc17_TMS_Data_Career_Meeting_Rating
GROUP BY Employee_ID
ORDER BY 1

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191749

SELECT Employee_ID, Meeting_Year, GROUP_CONCAT(Manager_Readiness_Rating)
FROM dbo.v_sc17_TMS_Data_Career_Meeting_Rating
GROUP BY Employee_ID, Meeting_Year

Upvotes: 2

Related Questions