Sql-server – Need a faster method to concatenate rows into column(s)–currently using CTEs

ctesql-server-2008-r2

Currently, we are using a view which I created that pulls data from a number of tables. The major factor about this is getting the view to specifications involves a mix of CTEs and string concatenating to combine multiple rows into one column (multiple times).
While the view works, users have recently complained about it being too slow.
Currently, the view setup looks something like this:

;
With thing1 AS
(
SELECT DISTINCT
            /* huge block of code with joins and such  
            that gets the individual fields and concatenates  
            personnel names to one row */
)
,
thing2 AS
(
    SELECT  TaskName,
            Company,
            Lease,
            TaskBegin,
            TaskEnd,
            Field,
            Personnel,
            Invoice,
            InvDate,
            InvTot,
            ROW_NUMBER() OVER (Partition by TaskName ORDER BY [TaskName] ASC) rn
    FROM thing1 
)

SELECT  TaskName,
        Company,
        Lease,
        Field,
        ISNULL(Personnel, '') as 'Personnel',
        TaskBegin,
        TaskEnd,
        ISNULL (STUFF( (SELECT ', ' + Invoice 
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'Invoice',
        ISNULL (STUFF( (SELECT ', ' + InvDate 
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceDate',
        ISNULL (STUFF( (SELECT ', ' + InvTot 
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceTotal'
    FROM thing2 WHERE rn = 1

GO

So it works as intended. Problem is, once I get to that final SELECT that concatenates the Invoice, InvDate, and InvTot, the query goes from not needing a single second (that is, a straight SELECT from thing2) to taking up to 7 seconds to run its course. (Average time is about 5 seconds.) I'm fairly confident that part of the blame lies in the ever-growing database–this view loads much faster on the test environment, which has far fewer records in it.

My question is thus: Is it possible to go about this in a way that is faster and less resource-intensive on the DB?

Best Answer

Looks like the issue in your code is multiple access to the CTEs and all the complicated logic you have in there. You should try to do everyting in a single pass with UNPIVOT/PIVOT. I did something similar here: http://spaghettidba.com/2011/10/13/concatenating-multiple-columns-across-rows/
In my example you will find the concatenation of sentences in different languages (not exactly what you're after), but it seems to me that you could use the same technique.