How do I combine rows

1

Hi all

Below is a truncated SQL code that I am using. It produces the attached dataset (the top portion), but I want to combine the 3 lines into one where the ReasonForVisitNM is all on one line (bottom portion). I truncated the code because its rather long with a lot of columns but didn't think it was necessary to include. If it is please let me

Untitled
Untitled1597×343 16.5 KB
know. Any assistance would be appreciated.

Select Distinct
	 #immunoTxCohort.PatientID
	,#immunoTxCohort.PatientNM
	,#immunoTxCohort.BirthDTS
	,#immunoTxCohort.SubscriberID
	,#immunoTxCohort.HA_PEID as Tx_PEID
	,ref_rfv.ReasonForVisitNM
2

hi hope this helps .. this is a SQL Server T-SQL Forum .. there are several ways to do this !!

i have chosen FOR XML .. way

please click arrow to the left for drop create data script 
create table immunoTxCohort ( PatientID int , PatientNM varchar(100) , BirthDTS date , SubscriberID int , Tx_PEID int , ReasonForVisitNM varchar(100) )
GO

insert into immunoTxCohort values 
(1111,'Blow Joe','10/07/34',2222,3333,'FATIGUE' ),
(1111,'Blow Joe','10/07/34',2222,3333,'FEVER' ),
(1111,'Blow Joe','10/07/34',2222,3333,'SHORTNESS OF BREATH' )
GO

SELECT DISTINCT ST2.PatientID , ST2.PatientNM  , ST2.BirthDTS  , ST2.SubscriberID  , ST2.Tx_PEID , 
    SUBSTRING(
        (
            SELECT ','+ST1.ReasonForVisitNM  AS [text()]
            FROM immunoTxCohort ST1
            WHERE ST1.PatientID = ST2.PatientID AND ST1.PatientNM = ST2.PatientNM AND ST1.BirthDTS = ST2.BirthDTS AND ST1.SubscriberID = ST2.SubscriberID AND ST1.Tx_PEID = ST2.Tx_PEID 
            ORDER BY ST1.PatientID , ST1.PatientNM  , ST1.BirthDTS  , ST1.SubscriberID  , ST1.Tx_PEID  
            FOR XML PATH ('')
        ), 2, 1000) immunoTxCohort
FROM immunoTxCohort ST2 
go

image

image

4

Assuming you want the reasons listed in chronological order, I guessed at the column name for the ORDER BY clause in the subquery, Naturally change it to match your other data. Other tweaking may be necessary since I couldn't test the code myself.

Select Distinct
	 iTC.PatientID
	,iTC.PatientNM
	,iTC.BirthDTS
	,iTC.SubscriberID
	,iTC.HA_PEID as Tx_PEID
	,ref_rfv.ReasonForVisitNM
From #immunoTxCohort iTC
Outer Apply (
    Select Stuff((
        Select ', ' + ReasonForVisitNM 
        From dbo.ref_rfv 
        Where ref_rfv.PatientID = iTC.PatientID
        Order By ref_rtv.VisitDate
        For XML PATH(''), TYPE 
    ).value('.', 'varchar(max)'), 1, 2, '')
) As ref_rfv
5

Thanks Harish and Scott I was able to complete the task using both of your advice.