condensing a pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

condensing a pivot table

Author

Topic

gurpleman
Starting Member

2 Posts

Posted - 2007-03-01 : 14:32:31

I wonder if anybody sees anything immediately wrong here:

I'm pivoting a table of diagnoses per patient (identified and grouped by visit_id), to include the diagnosis, the level of certainty of the diagnosis, and a handful of confirmation methods (H&E, CT, MRI, X-Ray, or Injection), as follows:

SELECT visit_id,
-- Dx 01
--Diagnosis
max(case dx.dx_cnt when 1 then dx.dx end) as dx01,
--Certainty
max(case (case dx.dx_cnt when 1 then dx.certainty end)
when 0 then 'possible'
when 1 then 'probable'
when 2 then 'definite' end) as certainty01,
--Confirmation method
max(case dx.dx_cnt when 1 then
(case dx.confirm_he when 0 then 0 when 1 then 1 else -1 end)
end) as confirmHE01,
max(case dx.dx_cnt when 1 then
(case dx.confirm_ct when 0 then 0 when 1 then 1 else -1 end)
end) as confirmCT01,
max(case dx.dx_cnt when 1 then
(case dx.confirm_mri when 0 then 0 when 1 then 1 else -1 end)
end) as confirmMRI01,
max(case dx.dx_cnt when 1 then
(case dx.confirm_xr when 0 then 0 when 1 then 1 else -1 end)
end) as confirmXR01,
max(case dx.dx_cnt when 1 then
(case dx.confirm_inj when 0 then 0 when 1 then 1 else -1 end)
end) as confirmINJ01

-- *** above block is repeated for 8 counts, to pivot up to 8 diagnoses. ***

FROM (
select visit_id, dx, certainty,
confirm_he, confirm_ct, confirm_mri, confirm_xr, confirm_inj, confirm_oth,
(
select count(*) from pnt02_enrollment_dx as dx2
where dx2.visit_id = dx1.visit_id
and dx2.pnt02_enrollment_dx_id <= dx1.pnt02_enrollment_dx_id
) as dx_cnt
from pnt02_enrollment_dx dx1
) dx

group by
visit_id

order by
visit_id

Seems like I should be able to come up with a construct where I check dx.dx_cnt once and then spit out all the fields for that specific diagnosis (rather than repeating the "max(case dx.dx_cnt when 1 then" clause 7 times. At this point, I'm pretty resigned to the idea of keeping this construction, but I was wondering there's one of those but-don't-you-know-about-this-other-widely-known-way-to-do-this things.

Kyle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources