How to change column name when we are using case ? SQL Server query

user_10915616 0
Viewed 1297

I am trying to get result in one row using case statement it is showing me correct result but in two rows let me show you my query,

select  
    v.Id,
    v.VisitNumber,
    v.EndTime as VisitEndTime,
    case when srh.ReviewerRole = 2 then srh.CreatedOn end as TeamLeaderApproval,
    case when srh.ReviewerRole = 4 then srh.CreatedOn end as ProgramManagerApproval
from
    visit v 
inner join 
    SurveyReviewHistory srh on v.Id = srh.VisitId 
where 
    v.Status = 5 
    and v.Id = 5957

it is showing me result in this format,

Id  VisitNumber VisitEndTime TeamLeaderApproval ProgramManagerApproval
5957 Visit_U_6   2019-05-26     2019-05-26             NULL
5957 Visit_U_6   2019-05-26     NULL                2019-05-26

I want to get result in one row if Reviewrole 2 then show createdon time in team leader column or if review role 4 then show createdon time in program manager column

Hopes for your suggestion

Thanks

sql-server-2012 case tsql sql-server
2 Answers

One way is to use conditional aggregation:

select  v.Id,
        v.VisitNumber,
        v.EndTime as VisitEndTime,
        max(case when srh.ReviewerRole = 2 then srh.CreatedOn end) as TeamLeaderApproval,
        max(case when srh.ReviewerRole = 4 then srh.CreatedOn end) as ProgramManagerApproval
from  visit v 
inner join SurveyReviewHistory srh 
    on v.Id = srh.VisitId 
where v.Status = 5 
and v.Id = 5957
group by v.Id, v.VisitNumber, v.EndTime

The idea is quite simple - you group by all the columns that give identical results in the original query.
Then, you use max to get the value of the case expression because when you are using max between two values where one of these values is null, the other value will be the value that's selected by the max aggregation function.

user_30945330

You can try this:

SELECT DISTINCT K.Id,
       K.VisitNumber,
       K.VisitEndTime,
       FIRST_VALUE(K.TeamLeaderApproval) OVER(PARTITION BY K.Id,K.VisitNumber, K.VisitEndTime ORDER BY K.TeamLeaderApproval DESC) AS TeamLeaderApproval,
       FIRST_VALUE(K.ProgramManagerApproval) OVER(PARTITION BY K.Id,K.VisitNumber, K.VisitEndTime ORDER BY K.ProgramManagerApproval DESC) AS ProgramManagerApproval
From  (
   select  
     v.Id,
     v.VisitNumber,
     v.EndTime as VisitEndTime,
     case when srh.ReviewerRole = 2 then srh.CreatedOn end as TeamLeaderApproval,
     case when srh.ReviewerRole = 4 then srh.CreatedOn end as ProgramManagerApproval
   from visit v 
      inner join 
          SurveyReviewHistory srh on v.Id = srh.VisitId 
    where 
       v.Status = 5 and v.Id = 5957
  ) as K
user_16668000
Related