Sql Query modification - 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 2008 Forums

Transact-SQL (2008)

Sql Query modification

Author

Topic

newkon12
Starting Member

4 Posts

Posted - 2014-04-15 : 01:26:00

This my query and this is not helping as per required output

SELECT
e.patientid,
e.indexpatient,
e.indexstudy,
e.studydatetime,
e.lastname,
e.firstname,
p.parameterID,
sum(p.resultvalue) as ResultValue

FROM dbcreators.gems_examview e,
dbcreators.parameter p

WHERE p.parameterID
in
('AVA (VTI)', '2D/Ao Root Diam', '2D/Ao asc Diam')

and
e.studydatetime<'2014-02-28' and e.studydatetime>'2009-04-12' and
p.indexpatient = e.indexpatient and p.indexstudy = e.indexstudy and
p.indexseries = e.indexseries and p.resultno = -1
group by e.patientid,
e.indexpatient,
e.indexstudy,
e.studydatetime,
e.lastname,
e.firstname,
p.parameterID
order by patientid desc

Current Result of this query is

patientid indexpatient indexstudy studydatetime ID RV
---------------------------------------------------------------------------------
AA9805 9805 5 9/10/09 12:51 PM AVA 0.0001
AA8991 8991 2 7/10/09 1:41 PM AVA 0.00011
AA8886 8886 3 7/27/09 6:59 AM 2D 0.08
AA8733 8733 5 4/17/09 2:38 PM 2D 0.0002
AA8463 8463 4 8/10/09 7:21 AM 3D 0.0001
AA7622 7622 9 7/16/09 12:23 PM 3D 0.00010
A7622 7622 7 5/6/09 9:23 AM 3D 0.0049

But I need below output where AVA,2D AND 3D as column header and below their value

patientid indexpatient indexstudy studydatetime AVA 2D 3D
---------------------------------------------------------------------------------
AA9805 9805 5 9/10/09 12:51 PM 0.0001
AA8991 8991 2 7/10/09 1:41 PM 0.00011
AA8886 8886 3 7/27/09 6:59 AM 0.08
AA8733 8733 5 4/17/09 2:38 PM 0.0002
AA8463 8463 4 8/10/09 7:21 AM 0.0001
AA7622 7622 9 7/16/09 12:23 PM 0.00010
A7622 7622 7 5/6/09 9:23 AM 0.0049

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-15 : 11:27:51

one way is to change your SELECT statement to something like this:


SELECT e.patientid,
       e.indexpatient,
       e.indexstudy,
       e.studydatetime,
       e.lastname,
       e.firstname,
       sum(case when p.parameterID = 'AVA (VTI)' then p.resultvalue end)       as [AVA (VTI)],
       sum(case when p.parameterID = '2D/Ao Root Diam' then p.resultvalue end) as [2D/Ao Root Diam],
       sum(case when p.parameterID = '2D/Ao asc Diam' then p.resultvalue end)  as [2D/Ao asc Diam]

Be One with the Optimizer
TG

newkon12
Starting Member

4 Posts

Posted - 2014-04-16 : 02:18:47

Thanks it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources