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?
with cte
as (select patientname
,temperature
,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
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?