need a group by that concatenates data

Question

Post reply

need a group by that concatenates data

Linehand

SSC Veteran

Points: 201
More actions
July 24, 2003 at 6:26 am

#80912

I need to do a group by that results in one row per patient but concatenates data rather than summarises it.

Check_type table is a reference table that holds 200 entries 15 of which will be checktype group 25 cardiac diagnosis.

SPell_Check holds 10-15 thousand rows of cardiac patient data of which a small subset is diagnosis.

Given small sample data below I need some SQL which could be used to produce a temporary table which concatenates the diagnosis also shown below is how the report will look.

Many thanks.

Check_type table - sintCheckTypeGroup = 25 holds all the various individual cardiac diagnosis

sintCheckType vchrDesc CheckTypeGroup

2501 Angina 25

2502 Heart Failure 25

2503 PTCA/Stent 25

SPell_Check table all the cardiac diagnosis for a patient for each phase of care during a cardiac spell.

vchrPatientNo sintCheckType sintPhase

Patient A 2501 1

Patient A 2502 1

Patient A 2503 1

Patient B 2501 1

Patient C 2501 2

Patient E 2502 1

Patient F 2502 1

Patient F 2503 1

TempTable

vchrDiagnosis vchrPatientNo sintPhase

Angina Patient B 1

Angina Patient C 2

Heart Failure Patient E 1

Angina ,Heart Failure Patient F 1

Angina,Heart Failure , PTCA/Stent Patient A 1

Report would read:

Angina ………other data from other tables

Patient B Patient C

Heart Failure

Patient E

Angina & Heart Failure

Patient F

Angina & Heart Failure & PTCA/Stent

Patient A

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 1

You might try seomthing like this:

/* User Defined Function to concatentate data */

create function [dbo].[fn_ListOfConditions] (@patientid varchar(25) )

returns varchar(255) as

begin

declare @string varchar(255)

set @string = ''

select @string = @string + vchrDesc + ''

from SPell_Check a, Check_Type b

where a.vchrPatientNo = @patientid

and a.sintCheckType = b.sintCheckType

return (@string)

end

/* Get list of patients with conditions */

select distinct vchrPatientNo , dbo.fn_ListOfConditions(vchrPatientNo)

from SPell_Check

where .....

You might want to put the results into a temporary table so that you can then do another concatentation function on the temp table to format the output as you required.

Hope this helps.

Jeremy

PS I haven't checked the syntax but I trust you can fix any errors. Also, I'm not sure about performance - it depends on so many factors that this approach might not be suitable for you. The only way to find out is to try it and see.

Robbac Ten Centuries Points: 1090 More actions · Answer 2

Hi Linehand!

Wow, at the first glance of your question it looked quite simple to come up with a sulotion for you. But after creating your sampletables and reading the question word by word I understand that you got into problems.

I don't like the sulotion that I have come up with, but I can't find a way to get around multiple cursors. I can accept a cursor, but having one cursor creating another cursor... that's another issue.

What I do below is:

Cursor 1. Get unique vchrPatientNo from SPell_Check

Cursor 2. Get all vchrDesc for this vchrPatientNo



SET NOCOUNT ON



DECLARE	@vchrPatientNo	VARCHAR(25),

		@sintPhase		INT,

		@tempDiagnosis	VARCHAR(1000),

		@vchrDiagnosis	VARCHAR(1000)



-- Create the output table

CREATE TABLE #TempTable (

	vchrDiagnosis	VARCHAR(1000),

	vchrPatientNo	VARCHAR(25),

	sintPhase		INT )



-- Declare the cursor for holding unique vchrPatientNo

DECLARE	c_patient CURSOR FOR

	SELECT	DISTINCT

			vchrPatientNo,

			sintPhase

	FROM	SPell_Check

OPEN c_patient

FETCH NEXT FROM c_patient INTO @vchrPatientNo, @sintPhase



-- Irritrate all the vchrPatientNo

WHILE @@FETCH_STATUS >= 0

BEGIN

	-- Reset an temporary variable

	SELECT	@vchrDiagnosis = ''



	-- Declare a cursor to hold all the vchrDesc for this vchrPatientNo

	DECLARE	c_diagnosis CURSOR FOR

		SELECT	ct.vchrDesc

		FROM	Check_type ct

				INNER JOIN SPell_Check sc ON ct.sintCheckType = sc.sintCheckType

		WHERE	sc.vchrPatientNo = @vchrPatientNo

		ORDER BY

				ct.vchrDesc

	OPEN c_diagnosis



	-- Irritrate all the vchrDesc

	FETCH NEXT FROM c_diagnosis INTO @tempDiagnosis

	WHILE @@FETCH_STATUS >= 0

	BEGIN

		-- If its the first just add this vchrDesc to the temporary variable

		IF ( DATALENGTH(@vchrDiagnosis) = 0 )

			SELECT	@vchrDiagnosis = @tempDiagnosis

		-- else add a comma-sign in front for delimeter

		ELSE

			SELECT	@vchrDiagnosis = @vchrDiagnosis + ', ' + @tempDiagnosis



		FETCH NEXT FROM c_diagnosis INTO @tempDiagnosis

	END

	-- Close and drop the innermost cursor since

	-- it will be created again in the next loop

	CLOSE c_diagnosis

	DEALLOCATE c_diagnosis



	-- Insert the row into the output table for this vchrPatientNo

	INSERT INTO #TempTable (

		vchrDiagnosis,

		vchrPatientNo,

		sintPhase )

	VALUES (

		@vchrDiagnosis,

		@vchrPatientNo,

		@sintPhase )



	FETCH NEXT FROM c_patient INTO @vchrPatientNo, @sintPhase

END



-- Close and drop the outer cursor

CLOSE c_patient

DEALLOCATE c_patient



-- Return the result

SELECT	vchrDiagnosis,

		vchrPatientNo,

		sintPhase

FROM	#TempTable

ORDER BY

		vchrDiagnosis



GO

This will result in:



vchrDiagnosis				vchrPatientNo	sintPhase

=====================================	=============	=========

Angina					Patient B	1

Angina					Patient C	2

Angina, Heart Failure, PTCA/Stent	Patient A	1

Heart Failure				Patient E	1

Heart Failure, PTCA/Stent		Patient F	1

Best of luck,

robbac

___the truth is out there___

robbac
___the truth is out there___