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.