Rows to columns error - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Rows to columns error

Author

Topic

jwwirds
Starting Member

3 Posts

Posted - 2007-07-18 : 10:33:43

Hi,

In our hospital database there can be several rows of diagnosis per patient. I want to build a query that concatenates these into one column. On this forum several topics deal with this subject. I now have te following code:

CREATE Function dbo.ConcatProcedure(@PatientId int)

RETURNS VARCHAR(8000)

AS

Begin
DECLARE @Output VARCHAR(8000)

SET @Output = ''

Select @Output =
Case @Output
When '' THEN TB_PROCEDURE_NAME.NAME
Else @Output + ', ' + TB_PROCEDURE_NAME.NAME
End

From Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS
inner Join TB_PATIENT_SESSION on TB_PATIENT_SESSION.PATIENT_SESSION_ID = Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID
inner Join TB_SESSION_DETAILS on TB_SESSION_DETAILS.SESSION_ID = Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.SESSION_ID
Left Join TB_DISEASE_NAME on TB_DISEASE_NAME.ICD_ID = TB_SESSION_DETAILS.DISEASE_ICD_ID
Left Join TB_PROCEDURE_NAME on TB_PROCEDURE_NAME.ICD_ID = TB_SESSION_DETAILS.PROCEDURE_ICD_ID

Where patient_id = @PatientId

And Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID In
(Select MAX(Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID)
From Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS
Inner Join TB_PATIENT_SESSION on TB_PATIENT_SESSION.PATIENT_SESSION_ID = Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID
Where patient_id = @PatientId)

And TB_SESSION_DETAILS.Qualifier_Id = 4

ORDER BY TB_PROCEDURE_NAME.NAME

RETURN @Output
End

Select Patients.Patientid, Patients.HospitalNumber, Patients.FirstName,
Patients.LastName, Patients.AddmissionDate, Patients.DischargeDate, dbo.ConcatProcedure(Patients.Patientid)

From Patients

This code generates the following error: Incorrect syntax near the keyword 'Select'. I have been working at this for a whole day now and can't solve it. Does anyone here have a solution?

Thanks,

Jan-Willem Wirds

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-18 : 19:38:58

what is your SP Out put?

hey

jwwirds
Starting Member

3 Posts

Posted - 2007-07-19 : 03:11:37

Can you enlighten me as to what that is? As a novice in SQL programming I am not familiar with all the terms used. Is this the output I expect from the code?

quote:
Originally posted by hey001us

what is your SP Out put?

hey

jwwirds
Starting Member

3 Posts

Posted - 2007-07-19 : 04:22:02

I had a close look at the original code by "kristen" on this forum. The solution was very easy indeed. I had to insert "GO" commands after the create function code and select statements! Below is the code that works as I intended:

CREATE Function dbo.ConcatProcedure(@PatientId int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Output VARCHAR(8000)

SET @Output = ''

Select @Output =  
	Case @Output 
		When '' THEN TB_PROCEDURE_NAME.NAME  
		Else @Output + ', ' + TB_PROCEDURE_NAME.NAME
	End

From Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS
	inner Join TB_PATIENT_SESSION on TB_PATIENT_SESSION.PATIENT_SESSION_ID = Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID
	inner Join TB_SESSION_DETAILS on TB_SESSION_DETAILS.SESSION_ID = Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.SESSION_ID
	Left Join TB_DISEASE_NAME on TB_DISEASE_NAME.ICD_ID = TB_SESSION_DETAILS.DISEASE_ICD_ID
	Left Join TB_PROCEDURE_NAME on TB_PROCEDURE_NAME.ICD_ID = TB_SESSION_DETAILS.PROCEDURE_ICD_ID

Where patient_id = @PatientId

And Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID In
	(Select MAX(Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID)
	From Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS
	Inner Join TB_PATIENT_SESSION on TB_PATIENT_SESSION.PATIENT_SESSION_ID = Xref_TB_PATIENT_SESSION_TB_SESSION_DETAILS.PATIENT_SESSION_ID
	Where patient_id = @PatientId)

And TB_SESSION_DETAILS.Qualifier_Id = 4

ORDER BY TB_PROCEDURE_NAME.NAME

RETURN @Output

End

GO

Select Patients.Patientid, Patients.HospitalNumber, Patients.FirstName, 
Patients.LastName, Patients.AddmissionDate, Patients.DischargeDate, dbo.ConcatProcedure(Patients.Patientid) "ICD 10" 

From Patients 

GO

DROP function dbo.ConcatProcedure

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources