For the below SQL I am getting 2 rows for PRV and CUR (as the table CVL has 2 rows for the same client ID)
How can I get the values in one row as Previous and Current location ?
Select CVL.Status, CVL.Locationname,
case when cvl.status='prv' AND CVL.LocationName like 'RGH-ED%' then cvl.locationname end as 'Previous Location',
case when cvl.status='cur' then cvl.locationname end as 'Current Location',
CVL.TransferRequestDtm,CV.ClientGUID,CV.ClientDisplayName, OCMI.Name 'Order Entered', CV.IDCode 'RHRN',
O.Entered, CV.VisitIDCode 'Patient Encounter',
O.IDCode 'Order ID', O.OrderStatusCode 'Order Status', O.Entered 'Date Entered', O.RequestedDate 'Order Requested Date'
FROM CV3Clientvisitlocation CVL
JOIN CV3ClientVisit CV ON CV.GUID=CVL.ClientVisitGUID
JOIN CV3Order O ON O.ClientVisitGUID=CV.GUID AND O.ChartGUID=CV.ChartGUID AND O.ClientGUID=CV.ClientGUID
JOIN CV3OrderCatalogMasterItem OCMI ON O.OrderCatalogMasterItemGUID=OCMI.GUID
WHERE
(CVL.Status='PRV' AND CVL.LocationName like 'RGH-ED%' OR
CVL.Status='CUR' AND CVL.LocationName like 'RGH-CCU%') AND
OCMI.CodedTimeGroupCode LIKE 'LAB%' AND (O.OrderStatusCode='AUA1' OR O.OrderStatusCode='PCOL' OR O.OrderStatusCode='PERF' OR O.OrderStatusCode='SPRV')
Without the table description and sample data, I can only guess, but this is what I came up with:
select cvl1.status
,cvl2.locationname as [Previous Location]
,cvl1.locationname as [Current Location]
,cvl1.transferrequestdtm
,cv.clientguid
,cv.clientdisplayname
,ocmi.name as [Order Entered]
,cv.idcode as rhrn
,o.entered
,cv.visitidcode as [Patient Encounter]
,o.idcode as [Order ID]
,o.orderstatuscode as [Order Status]
,o.entered as [Date Entered]
,o.requesteddate as [Order Requested Date]
from cv3clientvisitlocation as cvl1
left outer join cv3clientvisitlocation as cvl2
on cvl2.clientguid=cvl1.clientguid
and cvl2.status='PRV'
and cvl2.locationname like 'RGH-ED%'
inner join cv3clientvisit as cv
on cv.guid=cvl1.clientvisitguid
inner join cv3order as o
on o.clientvisitguid=cv.guid
and o.chartguid=cv.chartguid
and o.clientguid=cv.clientguid
and o.orderstatuscode in ('AUA1','PCOL','PERF','SPRV')
and o.entered>cast('2017-02-24' as date)
inner join cv3ordercatalogmasteritem as ocmi
on ocmi.guid=o.ordercatalogmasteritemguid
and ocmi.codedtimegroupcode like 'LAB%'
where cvl1.status='CUR'
and cvl1.locationname like 'RGH-CCU%'
;