Transform field values to boolean fields in query

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)

Transform field values to boolean fields in query

Author

Topic

erikm
Starting Member

1 Post

Posted - 2009-09-28 : 01:26:52

Hi,
I would like to transform via a select query a table of the form: PatientMeds(PatientID,Med)
to the form: TransformedPatientMeds(PatientID,Med1,Med2,...Med_n)

I know the names of the specific Meds and can specify them explicitly. After the transformation I would add criteria for example:
SELECT PatientID FROM TransformedPatientMeds WHERE Med1=TRUE AND Med2=FALSE

Thanks!

Example Data:
PatientMeds Table
PatientID,Med
1,Aspirin
1,Antacid
2,Ginseng

TransformedPatientMeds Query
PatientID,Aspirin,Antacid,Ginseng
1,True,True,False
2,False,False,True

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-28 : 01:29:51

use PIVOT operator. Refer to the Books Online for the syntax

KH
[spoiler]Time is always against us[/spoiler]

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-28 : 05:01:47

or this:

DECLARE @PatientMeds TABLE
(
PatientID int,
Med varchar(50)
)
insert into @PatientMeds values (1,'Aspirin')
insert into @PatientMeds values (1,'Antacid')
insert into @PatientMeds values (2,'Ginseng')

select PatientID,
MAX(case med when 'Aspirin' then 'True' else 'False' end) as 'Aspirin',
MAX(case med when 'Antacid' then 'True' else 'False' end) as 'Antacid',
MAX(case med when 'Ginseng' then 'True' else 'False' end) as 'Ginseng'
FROM @PatientMeds
GROUP BY PatientID

Edit: As you can see this is not an ideal solution for Med_n if n is a large no. or if n changes dynamically.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources