Normalizing flat row of data

[timscronin]

I have a view with patient data. It looks like below

patid date pulmdc pulmstatus endodc endostatus
100 4/1/05 10 Good null null
100 5/1/05 10 Good 12 Poor

I want to create sql which by each patient, by date, by these four fields ,get

patid 4/1/05 pulmdc-10 pulmstatus-good
patid 5/1/05 pulmdc-1 pulmstatus-good
patid 5/1/05 endodc-12 endostatus-poor

 

[AtomicWedgie]

Hi,

something like this:

select 'patid' as patid
, date as date
, 'pulmdc-' + pulmdc as dc
, 'pulmstatus-' + pulmststatus as status
from view
where pulmdc is not null
union
select 'patid' as patid
, date as date
, 'endodc-' + endodc as dc
, 'endostatus-' + endostatus as status
from view
where endodc is not null


Regards,

Atomic Wedgie

 

[timscronin]

Thanks, a little hesitant on using the unions, the view itself is a monster (6 tables and 6 ROJ's), there are actually 6 other fields I have to mimic this with but left them off for space reasons. I wonder if dumping into a temp table might be a better way to go