CODEWITHSUNDEEP
sqlsql-serverjoin
Lifewithsun
Lifewithsun

Reputation: 998

SQL Join Rows to column

I have left join between two tables where it returns two records I need that record should be converted from rows to column.

SQL join as follows:

select (case when l.PatientVisitCluster=1 then t.Result end) as t1,
       (case when l.PatientVisitCluster=2 then t.Result end) as t2
from tPatientLabTestData t left join 
     tPatientLabTest l
     on t.tPatientLabTestId = l.Id
where l.PatientId = @PatientId and 
      l.Volgnar = @Volgnar and 
      l.TestTypeListId = @LabTestId

It returns result like this.

enter image description here

I need desired result like below:

t1     t2
22     120

Upvotes: 0

Views: 93

Answers (2)

MtwStark
MtwStark

Reputation: 4058

You can also use PIVOT 

select PatientId, Volgnar, TestTypeListId, [1] t1, [2] t2
from tPatientLabTestData t 
left join tPatientLabTest l on t.tPatientLabTestId=l.Id
pivot (max(result) for PatientVisitCluster in ([1], [2])) p

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use aggregation:

select max(case when l.PatientVisitCluster = 1 then t.Result end) as t1,
       max(case when l.PatientVisitCluster = 2 then t.Result end) as t2
from tPatientLabTestData t left join
     tPatientLabTest l
     on t.tPatientLabTestId=l.Id
where l.PatientId = @PatientId and
      l.Volgnar = @Volgnar and
      l.TestTypeListId = @LabTestId;

If you wanted this for all combinations of the three columns, include group by:

select l.PatientId, l.Volgnar, l.TestTypeListId,
       max(case when l.PatientVisitCluster = 1 then t.Result end) as t1,
       max(case when l.PatientVisitCluster = 2 then t.Result end) as t2
from tPatientLabTestData t left join
     tPatientLabTest l
     on t.tPatientLabTestId=l.Id

group by l.PatientId, l.Volgnar, l.TestTypeListId;

Upvotes: 3

Related Questions