TSQL STUFF with XML PATH: Delimit a Series of Strings

Last post, we explored some useful cases where STUFF function can be used. This post we'll take a step further by combining STUFF with XML PATH to combine rows to a single string and separate the values with commas.

Let's remind ourselves the STUFF function syntax:
STUFF ( original_string, start_position , length , replaceWith_string )

 /**************************************************************/

 /* Delimit a string of text. Insert a comma between strings.*/

/**************************************************************/

 DECLARE @LogicalQueryProccessingOrder TABLE (

phase VARCHAR(10)

);

INSERT INTO @LogicalQueryProccessingOrder 

VALUES ('FROM'), ('WHERE'), ('GROUP BY'), ('HAVING'), ('SELECT'), ('ORDER BY');

/* this doesn't work because result has an extra comma at the end. Note this return in xml format */

SELECT phase + ', '

FROM @LogicalQueryProccessingOrder

FOR XML PATH('');

/* this one works */

SELECT STUFF (
       /* 1st parameter: original string */
       (SELECT phase + ', ' FROM @LogicalQueryProccessingOrder FOR XML PATH(''))
       /* 2nd parameter:  start position. This is at last comma position.

        , LEN((SELECT phase + ', ' FROM @LogicalQueryProccessingOrder FOR XML PATH('')))
       /* 3rd parameter: length: number of characters to be replace. This will delete the extra comma at the end. */

      , 1
       /* 4th parameter: string to be replace with. An empty string will act like an eraser. This will replace the last comma with an empty string */

    , '') AS LogicalQueryOrder; 

/* same result here */

SELECT STUFF((SELECT ', ' + phase FROM @LogicalQueryProccessingOrder FOR XML PATH('')) 

      , 1 

     , 1 

      ,'') AS LogicalQueryOrder; 

BOL STUFF Function: http://technet.microsoft.com/en-us/library/ms188043.aspx