SQLTeam.com | Weblogs | Forums

Pull first four dates and show them all on one line



I have a database with patients and the time their temperature was taken and the temperature value. I would like to pull the patient name and the first four temperatures taken and show them all on the same line. In other words the record would look like
Joe Smith, 98.6, 97.9, 98.0, 99.0
I was wondering how I can do this?

thank you for all your help!


This is one way of accomplishing your task:

with cte
  as (select patientname
            ,row_number() over(partition by patientname order by temperaturedatetime) as rn
        from yourtable
select patientname
      ,max(case when rn=1 then temperature else null)) as temperature1
      ,max(case when rn=2 then temperature else null)) as temperature2
      ,max(case when rn=3 then temperature else null)) as temperature3
      ,max(case when rn=4 then temperature else null)) as temperature4
  from cte
 where rn<=4
 group by patientname


Thank you very much!! This worked great!



Now the questions would be.... do you know how the good code that @bitsmed wrote works, what the technique is called so that you can describe it to others, and could you please explain why you need just the first 4 and what you're going to do with it?