CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012case
Doc
Doc

Reputation: 179

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

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

Upvotes: 3

Views: 2044

Answers (2)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

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

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 1

Related Questions