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.