pivot output - 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

General SQL Server Forums

New to SQL Server Programming

pivot output

Author

Topic

cjp
Yak Posting Veteran

69 Posts

Posted - 2008-12-08 : 05:34:55

I have written a pivot in SQLS 2005. This summarises patient exposures to a range of medical outcomes - one line per patient and several hundred fields.

Here is how I built the pivot (wrapping it in a cte so that I can output the result as a saved table):

with interim as
(
select <field list>
From
(select patnum, medical_code, marker
From medsource) t1
Pivot
(
Min(marker)
For medical_code IN
(
<field list>
)
) AS pvt
)
Select *
into matrix_output
from interim
order by interim.patnum

<field list> is defined as the distinct values held in the field [medical_code].

I have designed the underlying data so that every exposure is flagged to 1 in a marker field - every absence of an exposure is flagged to 99. I then use the min() function to identify any record which is a 1 for exposure.

The outcome is correct; eg:

patient 12c 136 137 168

123 1 null null 1

Is it possible to force the pivot to replace nulls with 0 - ie numeric zero?

Thanks.

Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 05:37:45

yup. use isnull() or coalesce() in select list to convert null to 0