need a group by that concatenates data

Question

Post reply

need a group by that concatenates data

Linehand

SSC Veteran

Points: 231
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
sintCheckTypevchrDesc CheckTypeGroup
2501 Angina 25
2502 Heart Failure25
2503 PTCA/Stent25
SPell_Check table all the cardiac diagnosis for a patient for each phase of care during a cardiac spell.
vchrPatientNosintCheckTypesintPhase
Patient A2501 1
Patient A2502 1
Patient A2503 1
Patient B2501 1
Patient C2501 2
Patient E2502 1
Patient F2502 1
Patient F2503 1
TempTable
vchrDiagnosis vchrPatientNosintPhase
AnginaPatient B1
AnginaPatient C2
Heart FailurePatient E1
Angina ,Heart FailurePatient F 1
Angina,Heart Failure , PTCA/Stent Patient A 1
Report would read:
Angina ………other data from other tables
Patient BPatient 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@vchrPatientNoVARCHAR(25),

@sintPhaseINT,

@tempDiagnosisVARCHAR(1000),

@vchrDiagnosisVARCHAR(1000)



-- Create the output table

CREATE TABLE #TempTable (

vchrDiagnosisVARCHAR(1000),

vchrPatientNoVARCHAR(25),

sintPhaseINT )



-- Declare the cursor for holding unique vchrPatientNo

DECLAREc_patient CURSOR FOR

SELECTDISTINCT

vchrPatientNo,

sintPhase

FROMSPell_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

DECLAREc_diagnosis CURSOR FOR

SELECTct.vchrDesc

FROMCheck_type ct

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

WHEREsc.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

SELECTvchrDiagnosis,

vchrPatientNo,

sintPhase

FROM#TempTable

ORDER BY

vchrDiagnosis



GO

This will result in:



vchrDiagnosisvchrPatientNosintPhase

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

AnginaPatient B1

AnginaPatient C2

Angina, Heart Failure, PTCA/StentPatient A1

Heart FailurePatient E1

Heart Failure, PTCA/StentPatient F1

Best of luck,

robbac

___the truth is out there___

robbac
___the truth is out there___

Linehand SSC Veteran Points: 231 More actions · Answer 3

Thanks Jermey I can sort out any syntax I just could find a way to approach the problem

Linehand SSC Veteran Points: 231 More actions · Answer 4

Thanks Robbac for your effort. It was very much appreciated. Jeremy's solution worked but I've learnt from both of you

thank-you both very very much