SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sql Query modification
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

newkon12
Starting Member

Pakistan
4 Posts

Posted - 04/15/2014 :  01:26:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/15/2014 :  11:27:51  Show Profile  Reply with Quote
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
Go to Top of Page

newkon12
Starting Member

Pakistan
4 Posts

Posted - 04/16/2014 :  02:18:47  Show Profile  Reply with Quote
Thanks it worked.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000