T-SQL with Stuff

The STUFF string function inserts a string into another string.  It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
It is very useful while generating a Comma-Separated List in a TSQL with the "XML Path" functionality.

For example, following query selects all Invoice-Numbers and the Invoice-Numbers + the status of those invoices of an order:

SELECT  
ODA_SUP_ID,  
ODA_SUPPLIER_NAME,  
ODA_CURRENCY_CODE,  
(SELECT  
    STUFF  
    (  
        (  
        SELECT ', ' + invoice_num AS [text()]
        FROM DOCS
        where order_num = PM_ORDER_DATA.ODA_ORDER_NO
        ORDER BY invoice_num
        FOR XML PATH ('')  
        ),1,1,''  
    )
) as Invoice_Numbers,
(SELECT  
    STUFF  
    (  
        (  
        SELECT ', ' + invoice_num + ': ' + status_index AS [text()]
        FROM P2P.dbo.DOCS
        where order_num = PM_ORDER_DATA.ODA_ORDER_NO
        ORDER BY  invoice_num
        FOR XML PATH ('')  
        ),1,1,''  
    )
) as Invoice_Numbers_Status

        
FROM PM_ORDER_DATA

Sources:
http://technet.microsoft.com/en-us/library/ms188043.aspx
http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=3934817c-1a03-4ac9-a0ba-55b2bfbaea0f&tid=72&tkey=uses-of-the-stuff-string-function