Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
6065 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  
 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.02 seconds. Powered By: Snitz Forums 2000