March 27, 2015 at 1:39 pm
Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?
I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right
Thanks
March 27, 2015 at 1:55 pm
This article should help you get what you need.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
March 27, 2015 at 2:28 pm
This may help also
;WITH DiagWithRowNum AS
(
SELECT
VisitGuid,
DiagnosisText,
ROW_NUMBER() OVER (Partition By VisitGuid Order BY VisitGuid) AS RowNum,
COUNT(*) OVER (PARTITION BY VisitGuid) AS DiagCount
FROM dbo.diag
),
BuildDiagString AS
(
SELECT VisitGuid, CAST(DiagnosisText as varchar(2000)) as DiagnosisText, RowNum, DiagCount FROM DiagWithRowNum WHERE RowNum = 1
UNION ALL
SELECT
D.VisitGuid, CAST(B.DiagnosisText + ', ' + D.DiagnosisText AS varchar(2000)) as DiagnosisText, D.RowNum, D.DiagCount
FROM DiagWithRowNum AS D
INNER JOIN BuildDiagString AS B ON D.VisitGuid = B.VisitGuid AND D.RowNum = B.RowNum + 1
)
SELECT
VisitGuid,
DiagnosisText
FROM BuildDiagString
WHERE RowNum = DiagCount
March 27, 2015 at 2:49 pm
ABAS101 (3/27/2015)
This may help also;WITH DiagWithRowNum AS
(
SELECT
VisitGuid,
DiagnosisText,
ROW_NUMBER() OVER (Partition By VisitGuid Order BY VisitGuid) AS RowNum,
COUNT(*) OVER (PARTITION BY VisitGuid) AS DiagCount
FROM dbo.diag
),
BuildDiagString AS
(
SELECT VisitGuid, CAST(DiagnosisText as varchar(2000)) as DiagnosisText, RowNum, DiagCount FROM DiagWithRowNum WHERE RowNum = 1
UNION ALL
SELECT
D.VisitGuid, CAST(B.DiagnosisText + ', ' + D.DiagnosisText AS varchar(2000)) as DiagnosisText, D.RowNum, D.DiagCount
FROM DiagWithRowNum AS D
INNER JOIN BuildDiagString AS B ON D.VisitGuid = B.VisitGuid AND D.RowNum = B.RowNum + 1
)
SELECT
VisitGuid,
DiagnosisText
FROM BuildDiagString
WHERE RowNum = DiagCount
Using a recursive CTE to concatenate strings is not a good idea as it will be slower that the previous option.
March 27, 2015 at 3:01 pm
I work in healthcare and we use close to this same code at a claim level instance. So for one claim for a single visit, it can have up to 5 diagnosis codes. We don't pull in the descriptions, but we list the diagnosis code itself (which is not a guid btw). There is no performance gain or loss at this level. Now for reporting when building a list of most common billed diagnosis codes, we use a separate process. I'm curious as to your link and the improvements on a larger dataset, so I'll definitely test that one and see what it gains us.
March 27, 2015 at 3:22 pm
Nice. Below are the stats based on changing it to the link Luis provide. Though one single call isn't noticeable to the eye, I would imagine if you are doing this at a much larger data set, it would far more noticeable. Thanks Luis
CPU time = 0 ms, elapsed time = 5 ms
to
CPU time = 0 ms, elapsed time = 0 ms.
March 28, 2015 at 3:25 am
Quick point, there is a hidden speed trap in the XML concatination, consider this code
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_DIAG') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DIAG;
CREATE TABLE dbo.TBL_SAMPLE_DIAG
(
SD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_DIAG_SD_ID PRIMARY KEY CLUSTERED
,VisitGUID UNIQUEIDENTIFIER NOT NULL
,DiagnosisText NVARCHAR(MAX) NOT NULL
)
;
INSERT INTO dbo.TBL_SAMPLE_DIAG (VisitGUID,DiagnosisText)
VALUES
('67341E48-6D70-4CFB-8A5B-003901167C14',N'Hematuria')
,('67341E48-6D70-4CFB-8A5B-003901167C14',N'Dehydration')
,('67341E48-6D70-4CFB-8A5B-003901167C14',N'Renal failure')
,('67341E48-6D70-4CFB-8A5B-003901167C14',N'Anemia')
,('67341E48-6D70-4CFB-8A5B-003901167C14',N'Hyperkalemia')
,('67341E48-6D70-4CFB-8A5B-003901167C14',N'Severe hyponatremia')
,('67341E48-6D70-4CFB-8A5B-003901167C14',N'(profound hypochloremia)')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Mild bilateral hydronephrosis')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'HyperKalemia (60)')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Acute Kidney Injury: Abnormal Labs BUN 65 Cr 61 GFR 9')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Subtherapeutic tegretol levels')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Anemia (Hgb 9)')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Hypertension')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Coronary artery disease')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Sleep apnea')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Chronic obstructive pulmonary disease')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Diabetes mellitus')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Renal disease')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Gastroesophageal reflux disease')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Osteoporosis')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Hyperlipidemia')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'Hypothyroidism')
,('4E23BD63-C541-47D5-9024-0044FD16138A',N'h/o Bladder cancer and prostatic cancer')
,('252B61D6-B386-422C-AF01-0076DCEDD1E2',N'Fever')
,('252B61D6-B386-422C-AF01-0076DCEDD1E2',N'Dyspnea')
,('252B61D6-B386-422C-AF01-0076DCEDD1E2',N'Cough')
,('252B61D6-B386-422C-AF01-0076DCEDD1E2',N'Abnormal tests: (WBC = 210 c 22% bands)')
,('252B61D6-B386-422C-AF01-0076DCEDD1E2',N'Anemia (Hgb =85)')
,('252B61D6-B386-422C-AF01-0076DCEDD1E2',N'Thrombocytopenia (plt=129k)')
,('252B61D6-B386-422C-AF01-0076DCEDD1E2',N'Possible pneumonia Clinical picture does not suggest pulmonary embolism, congestive heart failure or myocardial infarction')
;
/* First collect all distinct key values, the cardinality of this
set will determine the number of scans in the concatination
step. Otherwise if only distinct is used in the concatination
then the number of scans = number of rows!
*/
RAISERROR (N'
-------------------------------------------------------------
CTE with distinct key values
-------------------------------------------------------------
',0,0)
SET STATISTICS IO,TIME ON;
;WITH BASE_DATA AS
(
SELECT DISTINCT
SD.VisitGUID
FROM dbo.TBL_SAMPLE_DIAG SD
)
/* Concatenation using FOR XML with an empty PATH
*/
SELECT
BD.VisitGUID
,STUFF((SELECT
N', ' + XD.DiagnosisText
FROM dbo.TBL_SAMPLE_DIAG XD
WHERE BD.VisitGUID = XD.VisitGUID
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'),1,2,'') AS CONCAT_DIAG_TEXT
FROM BASE_DATA BD;
SET STATISTICS IO,TIME OFF;
RAISERROR (N'
-------------------------------------------------------------
DISTINCT in the select
-------------------------------------------------------------
',0,0)
SET STATISTICS IO,TIME ON;
SELECT DISTINCT
SD.VisitGUID
,STUFF((SELECT
N', ' + XD.DiagnosisText
FROM dbo.TBL_SAMPLE_DIAG XD
WHERE SD.VisitGUID = XD.VisitGUID
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'),1,2,'') AS CONCAT_DIAG_TEXT
FROM dbo.TBL_SAMPLE_DIAG SD;
SET STATISTICS IO,TIME OFF;
Result (same for both queries)
VisitGUID CONCAT_DIAG_TEXT
------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
67341E48-6D70-4CFB-8A5B-003901167C14 Hematuria, Dehydration, Renal failure, Anemia, Hyperkalemia, Severe hyponatremia, (profound hypochloremia)
4E23BD63-C541-47D5-9024-0044FD16138A Mild bilateral hydronephrosis, HyperKalemia (60), Acute Kidney Injury: Abnormal Labs BUN 65 Cr 61 GFR 9, Subtherapeutic tegretol levels, Anemia (Hgb 9), Hypertension, Coronary artery disease, Sleep apnea, Chronic obstructive pulmonary disease, Diabetes me
252B61D6-B386-422C-AF01-0076DCEDD1E2 Fever, Dyspnea, Cough, Abnormal tests: (WBC = 210 c 22% bands), Anemia (Hgb =85), Thrombocytopenia (plt=129k), Possible pneumonia Clinical picture does not suggest pulmonary embolism, congestive heart failure or myocardial infarction
Statistics (IO,TIME)
-------------------------------------------------------------
CTE with distinct key values
-------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 6 ms, elapsed time = 6 ms.
Table 'TBL_SAMPLE_DIAG'. Scan count 4, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-------------------------------------------------------------
DISTINCT in the select
-------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
Table 'Worktable'. Scan count 33, logical reads 75, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_DIAG'. Scan count 4, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
March 28, 2015 at 2:37 pm
Thanks a lot. I used 2 CTE with ROW_NUMBER. It takes 2 sec to create 5743 resulting rows out of 14431 rows in _Diagnosis2015 table but after that the code continue to run indefinitely
;WITH DiagWithRowNum AS
(
SELECT
VisitGuid,
DiagnosisText,
ROW_NUMBER() OVER (Partition By VisitGuid Order BY VisitGuid) AS RowNum,
COUNT(*) OVER (PARTITION BY VisitGuid) AS DiagCount
FROM _Diagnosis2015
),
BuildDiagString AS
(
SELECT VisitGuid, CAST(DiagnosisText as varchar(3000)) as DiagnosisText, RowNum, DiagCount FROM DiagWithRowNum WHERE RowNum = 1
UNION ALL
SELECT
D.VisitGuid, CAST(B.DiagnosisText + ', ' + D.DiagnosisText AS varchar(3000)) as DiagnosisText, D.RowNum, D.DiagCount
FROM DiagWithRowNum AS D
INNER JOIN BuildDiagString AS B ON D.VisitGuid = B.VisitGuid AND D.RowNum = B.RowNum + 1
)
SELECT
VisitGuid,
DiagnosisText
FROM BuildDiagString
WHERE RowNum = DiagCount
March 30, 2015 at 1:01 pm
Luis proc works great yelding 14431 rows within 3 sec (both versions)
Thank you, guys!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply