Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > TSQL – Concatenate Rows using FOR XML PATH()

TSQL – Concatenate Rows using FOR XML PATH()

This is probably one of the most frequently asked question – How to concatenate rows? And, the answer is to use XML PATH.

For example, if you have the following data:

– © 2011 – Vishal (http://SqlAndMe.com)

USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            SUB.Name AS [Sub Category]
FROM        Production.ProductCategory CAT
INNER JOIN  Production.ProductSubcategory SUB
            ON CAT.ProductCategoryID = SUB.ProductCategoryID

image

The desired output here is to concatenate the subcategories in a single row as:

image

We can achieve this by using FOR XML PATH(), the above query needs to be modified to concatenate the rows:

– © 2011 – Vishal (http://SqlAndMe.com)
USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        – Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') – Select it as XML
                        ), 1, 1, '' )
                        – This is done to remove the first character (,)
                        – from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

Executing this query will generate the required concatenated values as depicted in above screen shot.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Rama
    February 10, 2012 at 8:29 pm

    Hey Thanks Buddy…

  2. March 29, 2012 at 9:55 pm

    This is cool! However when I use it the resulting column is a CLOB. Any ideas?

    • March 30, 2012 at 8:49 am

      Hi John,

      You can cast it explicitly to change resulting data type:
      CAST( STUFF……. 1,1, ” ) AS NVARCHAR(500)) AS [Sub Categories]

  3. Naveen
    April 25, 2012 at 4:00 pm

    Really Good information. Thxs Vishal

  4. Abhishek
    August 4, 2012 at 6:14 pm

    ple tell me the Table structure on which u apply XML path

  5. John
    August 25, 2012 at 12:57 am

    one minor observation – I believe a GROUP BY will be required for Category, and perhaps an ORDER BY as well?

  6. September 6, 2012 at 12:05 pm

    Thanks for this information with this i was able to make my task done…
    Here it is I just want to share it.

    DECLARE @testValue NVARCHAR(MAX) = N'{a}{b}{c}{d}';
    DECLARE @XMLTest XML = @testValue;
    DECLARE @result NVARCHAR(MAX);
    SET @result = STUFF((
    SELECT (‘,’ +
    STUFF(STUFF(
    CAST(s.query(‘text()’) AS NVARCHAR(MAX)),1,1,”),
    (LEN(CAST(s.query(‘text()’) AS NVARCHAR(MAX)))-1),1,”)) AS [text()]
    FROM @XMLTest.nodes(‘/rows/row’) t(s)
    FOR XML PATH(”)), 1, 1, ”);

    SELECT @result;

  7. September 6, 2012 at 12:07 pm

    Thanks for this information. With this I was able to get my work done.

    DECLARE @testValue NVARCHAR(MAX) = N'{a}{b}{c}{d}';
    DECLARE @XMLTest XML = @testValue;
    DECLARE @result NVARCHAR(MAX);
    SET @result = STUFF((
    SELECT (‘,’ +
    STUFF(STUFF(
    CAST(s.query(‘text()’) AS NVARCHAR(MAX)),1,1,”),
    (LEN(CAST(s.query(‘text()’) AS NVARCHAR(MAX)))-1),1,”)) AS [text()]
    FROM @XMLTest.nodes(‘/rows/row’) t(s)
    FOR XML PATH(”)), 1, 1, ”);

    SELECT @result;

  8. Fabio
    May 10, 2013 at 4:03 pm

    IS it possible to do the opposite?

    • JerryCic
      May 26, 2013 at 6:38 am

      Fabio: if you mean to return a table from a delimited list then yes.
      select * from dbo.split(‘,’,’a,b,cd,e,f,gh’)
      returns

      Value
      1 a
      2 b
      3 cd
      4 e
      5 f
      6 gh

      Here is the code:

      USE [yourDB]
      GO

      /****** Object: UserDefinedFunction [dbo].[Split] Script Date: 05/25/2013 21:06:38 ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(5120))
      RETURNS table
      AS
      RETURN (
      WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT
      pn + 1,
      stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
      )
      SELECT –pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 5120 END) AS Value
      FROM Pieces
      )

      GO

  9. John
    May 25, 2013 at 8:48 am

    Good Job on the Stuff utility. Using it now to process a join table in to a CSV derived table for use with an MVC app.

  10. Lynne
    June 1, 2013 at 1:11 am

    Very helpful!!! Thank you!!

  11. sud
    June 26, 2013 at 11:45 am

    exactly what exactly — for xml path( ”)— do here??

  12. Muhammad Hafeez
    August 3, 2013 at 12:08 am

    Great work! For the benefit of others, this is how I utilized your suggestion:

    select k.*, (SELECT cast(STUFF((SELECT ‘/’ + RTRIM(FlightNumber) + ‘,’ + RTRIM(DepartureDate)FROM [mydb.mytable] x where x.PNRID = k.PNRID FOR XML PATH(”)),1,1,” ) as nvarchar(500))) as [Itinerary]from
    (???)k
    order by k.passengername

  13. Nitin Rana
    September 6, 2013 at 12:14 pm

    Many thanks. Great tip.

  14. bairam khan
    October 28, 2013 at 5:27 pm

    what if same thing being done with data in another table..i mean use of join

  15. Ahmed Volcano
    January 7, 2014 at 3:19 am

    Its Great and helped me :)
    Thanks

  16. January 9, 2014 at 9:53 pm

    Nice tip ! Thanks a lot!

  17. Ben
    March 4, 2014 at 9:43 pm

    Great tip. How would you suppress a category if the results of the subcategory query were null, such as if there were no subcategories of bikes?

    • March 27, 2014 at 6:34 pm

      You can enclose it in a CTE or add a WHERE caluse as:
      WHERE CAT.ProductCategoryID IN (SELECT ProductCategoryID FROM Production.ProductSubcategory)

  18. Mark
    March 27, 2014 at 6:22 pm

    Great info… How could I limit the results to the first 4 records returned

    • March 27, 2014 at 6:30 pm

      You can use TOP clause inside sub-query to limit rows returned.

  19. Renee
    May 27, 2014 at 8:16 pm

    I just came across this. thank you it works great. However my concatenated field sometimes starts with a zero and it is dropping that zero…
    ie
    01
    03
    05
    16
    18
    results
    1051618 – this should be 01051618
    10305 – this should be 010305

    I have tried to cast the selected result and the final result but no change….

    –cast( (tried this first)
    STUFF(( SELECT cast(Code as varchar(2)) AS [text()]
    FROM Activity_Codes ac
    WHERE
    hpc.Activity_Code like ‘%’+ac.Code+’%’
    FOR XML PATH(”) ), 1, 1, ” )
    –as varchar(20))

    sorry if this is something simple – I am a self taught coder and still learning.

    • Renee
      May 27, 2014 at 8:32 pm

      figured it out! changed it to
      STUFF(( SELECT Code AS [text()]
      FROM Activity_Codes ac
      WHERE
      hpc.Activity_Code like ‘%’+ac.Code+’%’
      FOR XML PATH(”) ), 1, 0, ” ) — changed the 2nd 1 to 0

  20. Paul Pritchard
    May 28, 2014 at 5:02 pm

    Thanks very much for this – it has really helped me.
    My XML PATH command lists a collection of numbers. But I am battling to include an ORDER BY statement. Where ever I seem to put it I get a Syntax error!
    I’m using MS SQL Server 2012 Express.
    Please help me.
    Thanks again.
    Paul

  21. Stacy
    June 3, 2014 at 11:40 pm

    Thanks for this – it works great for my table. I was wondering how you would add Case statements to this, if for instance I wanted to name ‘Bike Stands’ to ‘Stands” or name a NULL value to ‘Something’.

  22. Matt
    July 14, 2014 at 6:38 pm

    I tried this and still get the results as rows and no string at all

    SELECT CAT.user_id AS [Category],
    STUFF(( SELECT ‘,’ + SUB.role_Name AS [text()]
    — Add a comma (,) before each value
    FROM role SUB
    WHERE
    SUB.role_id = CAT.role_id
    FOR XML PATH(”) — Select it as XML
    ), 1, 1, ” )
    — This is done to remove the first character (,)
    — from the result
    AS [Sub Categories]
    FROM user_role CAT

    Why?

  1. April 24, 2013 at 4:01 pm
  2. September 16, 2013 at 12:23 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 273 other followers

%d bloggers like this: