HOME | FERGUSON Digital Blog

Using FOR XML PATH and STUFF to CONCATENATE

MS SQL2008 Add comments

Have you ever wanted to concatenate a repeating column as a comma delimitated string in the same query result? Normally, I would use a Scalar Valued Function to COALESCE the values and return them to the calling query (as in the recent post here by Dustin Brisson), but there is a way to do this all in a single request.

SELECT      DISTINCT
         UserID,

         STUFF(
               (SELECT      ',' + SubTableUser.UserRole
               FROM      TableUser AS SubTableUser
               WHERE      SubTableUser.UserID = TableUser.UserID
               FOR XML PATH('')), 1, 1, '') AS UserRole

FROM      TableUser
ORDER BY   UserID

Above is a query with a sub-query. The parent query using DISTINCT against the record values that will remain the same during the repeated result set. The result set in this case happens because there are several users in this fictional database that have more than one user role assigned.

The sub-query, concatenates a comma in front of each additional result for the user role obtained when the user ID of the sub-query matches the distinct user ID of the parent query. This results in a very nice string with an awful leading comma.

You could strip out the comma whatever will display the results, but here we are using the STUFF command to take the first character, in this case a comma, and replace it with nothing. This results in a nice clean list of user roles for each distinct user ID.

If you find this post useful please leave a comment and let me know how you used the information.

6 responses to “Using FOR XML PATH and STUFF to CONCATENATE”

  1. Jeremy Halliwell on Thanks for this example, having been frustrated in SQL Server 2000 by the lack of an easy equivalent of MYSQL's GROUP_CONCAT() function, this does the job nicely, although it requires an upgrade to MS SQL 2008, but the express edition is free.
  2. izzy on neat ! Thanks! this is exactly what I need
  3. Tim Paulaskas on Great example! This is exactly what I needed to do.
  4. Jenny B on Hi - This looks really cool. But I tried to use it with multiple tables joining each other and I am stuck. I could not get the logic to work. Can you help?

    Basically, I have the 3 tables with sample data as below and I want to concatenate the last name after one another with a comma for each ID that has more than 1 EMPLID that share the same R_TYP_C.

    This is what I need for the result with the sample data shown from the tables below:

    CPO_I;R_TYP_C,R_TYP_X,EMPLID;L_NAME
    001;1;TECH_OWNER;1009,2001;STRANGE,STEVENS
    002;2;BA; 4001;NEWTONS
    Table1:
    CPO_I; EMPLID; R_TYP_C
    001; 1009; 1
    001; 2001; 1
    001; 3001; 4
    002; 4001; 2

    Table 2:
    EMPLID; L_NAME; EMAIL
    1009; Strange; strange.fname@yahoo.com
    2001; stevens; stevens.fname@yahoo.com
    3001; cyclop; cyclop.fname@gmail.com
    4001; newtons; newtons.fname@gmail.com

    Table3:
    R_TYP_C; R_TYP_X
    1; Tech_Owner
    2; BA
    3; Sec Cord
    4; Ret Author
  5. Bob H. on Well hell, you are a freaking genius. Just made my problem so much easier. Thanks!
  6. M. Brian Dunson on Wow...excellent example and just what I needed. Thanks very much!

Leave a Reply




© Copyright 1997-2014, All Rights Reserved Coldfusion and MS SQL2008
Powered by Mango Blog.   Design by FERGUSON Digital
o:Event name="beforeHtmlBodyEnd" />