Query: multiple columns from single column

[ellismatt]

Hi,

My database contains a register of X-rays which were taken in a hospital. Some patients have multiple X-ray images taken and consequently a single patient may have multiple entries in the original talbe. The fields I am considering are:
- patient ID
- type of X-ray (head, chest, leg, etc)
- date of X-ray

I am trying to construct a query which can output a single row for each individual patient, and has one colum showing the date of each type of X-ray. Ideally the columns would be:
- patient ID
- date of head X-ray
- date of chest X-ray
- date of leg X-ray
...and so on


The only way I can think of doing this is to produce multiple queries, one for each type of X-ray and then combine all these queries with an inner join on Patient ID, However this would make my database very messy as there are lots of types of X-ray and I need to repeat the same process for other things which go on in the hospital too.

Can anyone suggest a better way of doing this?
Thanks very much,
Matt

 

[r937]

select patientID
     , max(case when xraytype = 'head'
                then xraydate end)    as date_of_head_xray
     , max(case when xraytype = 'chest'
                then xraydate end)    as date_of_chest_xray
     , max(case when xraytype = 'leg'
                then xraydate end)    as date_of_leg_xray
  from xrays
group
    by patientID

notice that there is no ELSE in the CASE expressions -- this yields NULL where appropriate, and the MAX() function ignores those

r937.com | rudy.ca