-2

I have this table in SQL Server 2016 Express

TestDate    TestCode    TestResult
2017-08-30  HGB         5.3       
2017-09-01  HGB         6.1       
2017-09-03  HGB         6.8       
2017-09-06  HGB         7.9       
2017-09-13  HGB         8.5       
2017-08-30  EOS%        48.3      
2017-09-01  EOS%        47.1      
2017-09-03  EOS%        46.4      
2017-09-06  EOS%        47.3      
2017-09-13  EOS%        38.9

I need a query to return results like below:

TestDate    HGB     EOS%
2017-08-30  5.3     48.3      
2017-09-01  6.1     47.1      
2017-09-03  6.8     46.4      
2017-09-06  7.9     47.3      
2017-09-13  8.5     38.9

Sql Table And Results

Here is the code to create & populate the table

Create Table TestResult 
(
 TestDate   Datetime,
 TestCode   Char(10), 
 TestResult Char(10) 
)   

Insert Into TestResult Values('2017/08/30','HGB','5.3') 
Insert Into TestResult Values('2017/09/01','HGB','6.1') 
Insert Into TestResult Values('2017/09/03','HGB','6.8') 
Insert Into TestResult Values('2017/09/06','HGB','7.9') 
Insert Into TestResult Values('2017/09/13','HGB','8.5') 

Insert Into TestResult Values('2017/08/30','EOS%','48.3') 
Insert Into TestResult Values('2017/09/01','EOS%','47.1') 
Insert Into TestResult Values('2017/09/03','EOS%','46.4') 
Insert Into TestResult Values('2017/09/06','EOS%','47.3') 
Insert Into TestResult Values('2017/09/13','EOS%','38.9')
boop_the_snoot
  • 3,209
  • 4
  • 33
  • 44
Faiz Rahmathulla
  • 19
  • 5

1 Answers1

0

try this:

--Modify type testresult column

Create Table TestResult 
(
 TestDate   Datetime,
 TestCode   Char(10), 
 TestResult decimal(10, 2) 
)   

Insert Into TestResult Values('2017/08/30','HGB',5.3) 
Insert Into TestResult Values('2017/09/01','HGB',6.1) 
Insert Into TestResult Values('2017/09/03','HGB',6.8) 
Insert Into TestResult Values('2017/09/06','HGB',7.9) 
Insert Into TestResult Values('2017/09/13','HGB',8.5) 
Insert Into TestResult Values('2017/08/30','EOS%',48.3) 
Insert Into TestResult Values('2017/09/01','EOS%',47.1) 
Insert Into TestResult Values('2017/09/03','EOS%',46.4) 
Insert Into TestResult Values('2017/09/06','EOS%',47.3) 
Insert Into TestResult Values('2017/09/13','EOS%',38.9) 

select f1.TestDate, 
sum(case when f1.testcode='HGB' then f1.testresult else 0 end) HGB,
sum(case when f1.testcode='EOS%' then f1.testresult else 0 end) EOS
from TestResult f1
group by f1.TestDate

If you cant modify type column try this:

select f1.TestDate, 
max(case when f1.testcode='HGB' then f1.testresult else 0 end) HGB,
max(case when f1.testcode='EOS%' then f1.testresult else 0 end) EOS
from TestResult f1
group by f1.TestDate
Esperento57
  • 16,521
  • 3
  • 39
  • 45