How would I do this? - 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

How would I do this?

Author

Topic

scottstown
Starting Member

10 Posts

Posted - 2011-07-25 : 12:53:38

The following statement,

Select PatientID, cttest, XAtest, dxtest
FROM (
SELECT PatientID,
case when Modality = 'CT' THEN 'CT' end as CTtest,
case when Modality = 'XA' THEN 'XA' end as XAtest,
case when Modality = 'DX' THEN 'DX' end as DXtest
FROM localdbview
WHERE patientid = 1482815
)
AS ta

Gives me,

1482815 NULL NULL DX
1482815 CT NULL NULL

How would I go about combining the two rows in the example into one row, grouped by PatientID.

Thank you,
Scott

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 12:57:33

[code]
SELECT PatientID,
MAX(case when Modality = 'CT' THEN 'CT' end) as CTtest,
MAX(case when Modality = 'XA' THEN 'XA' end) as XAtest,
MAX(case when Modality = 'DX' THEN 'DX' end) as DXtest
FROM localdbview
GROUP BY PatientID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scottstown
Starting Member

10 Posts

Posted - 2011-07-25 : 13:25:50

Wow, thank you visakh16! I thought that MAX could only be used for numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 13:45:27

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources