July 24, 2003 at 6:26 am
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
July 24, 2003 at 7:13 am
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.
July 24, 2003 at 7:17 am
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___
July 24, 2003 at 7:21 am
Thanks Jermey I can sort out any syntax I just could find a way to approach the problem
July 24, 2003 at 8:00 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply