ChrisM@Work (8/29/2012)

---

Try putting the FOR XML PATH stringinator into a CROSS APPLY, where it can crunch up the rows of PSFDOC separately from the rest of the query. You might not even need the DISTINCT:

;WITH CTE AS (

SELECT --DISTINCT

LTRIM(RTRIM(P.IncidentIDRef)) AS MODIDREF,

P.JurisID,

P.EntDttm AS CREATEDDTTM,

P.DIDCU AS CREATEDPFIDREF,

'INC' AS MODCODE,

'C' RECTYPE,

x.Comment

FROM PSDOCI P

CROSS APPLY (

SELECT COMMENT = REPLACE(STUFF(

(SELECT '</P>' + C.FDNARR

FROM PSFDOC C

WHERE C.FDDOC# = P.DIDOC#

ORDER BY C.FDDOC#, C.FDLINE FOR XML PATH(''))

, 1, 10, ''), '</P>', '</P>')

) x

--JOIN PSFDOC T

--ON P.DIDOC# = T .FDDOC#

WHERE P.DIFILE = 'PLINCD'

AND (NOT P.DIDESC IN ('cad system narrative', 'dispatch narrative'))

AND P.DIDESC NOT LIKE 'Converted Cad Comment%'

)

SELECT

*,

CAST(CTE.COMMENT AS NVARCHAR(MAX)) AS COMMENT1,

ROW_NUMBER() OVER (PARTITION BY CTE .JURISID, CTE .MODIDREF ORDER BY CTE .JURISID, CTE .MODIDREF) - 1 AS SEQNO

FROM CTE