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

  1. Rama
    10.02.2012 08:29 | #1

    Hey Thanks Buddy…

  2. John B. Seals (@sparky)
    29.03.2012 09:55 | #2

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

  3. Vishal
    30.03.2012 08:49 | #3

    Hi John,

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

  4. Naveen
    25.04.2012 04:00 | #4

    Really Good information. Thxs Vishal

  5. Abhishek
    04.08.2012 06:14 | #5

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

  6. John
    25.08.2012 12:57 | #6

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

  7. Andamon A. Abilar
    06.09.2012 12:05 | #7

    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;

  8. andamonabilar
    06.09.2012 12:07 | #8

    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;

  9. Fabio
    10.05.2013 04:03 | #9

    IS it possible to do the opposite?

  10. John
    25.05.2013 08:48 | #10

    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.

  11. JerryCic
    26.05.2013 06:38 | #11

    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

  12. Lynne
    01.06.2013 01:11 | #12

    Very helpful!!! Thank you!!

  13. sud
    26.06.2013 11:45 | #13

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

  14. Muhammad Hafeez
    03.08.2013 12:08 | #14

    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

  15. Nitin Rana
    06.09.2013 12:14 | #15

    Many thanks. Great tip.

  16. bairam khan
    28.09.2013 05:27 | #16

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

  17. Ahmed Volcano
    07.01.2014 03:19 | #17

    Its Great and helped me ?
    Thanks

  18. zhamon
    09.01.2014 09:53 | #18

    Nice tip ! Thanks a lot!

  19. Ben
    04.03.2014 09:43 | #19

    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?

  20. Mark
    27.03.2014 06:22 | #20

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

  21. Vishal
    27.03.2014 06:30 | #21

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

  22. Vishal
    27.03.2014 06:34 | #22

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

  23. Renee
    27.05.2014 08:16 | #23

    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.

  24. Renee
    27.05.2014 08:32 | #24

    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

  25. Paul Pritchard
    28.05.2014 05:02 | #25

    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

  26. Stacy
    03.06.2014 11:40 | #26

    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’.

  27. Matt
    14.07.2014 06:38 | #27

    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?

  28. Ankit Pandey
    15.07.2014 04:08 | #28

    Suppose the first sub category is blank for Bikes so the solution provided by you will result something like this

    ,Road Bikes, Touring Bikes

    I want to avoid this comma coming at the starting position.

  29. Steve
    18.05.2015 03:10 | #29

    Brilliant!! Thanks Vishal

  30. Sreekanth G
    22.07.2015 09:32 | #30

    what is the role of as [text()] construct in this query?

  31. Sreekanth G
    22.07.2015 09:38 | #31

    I am assuming by now you have figured out. If user_role table has multiple rows, you will get as many rows in output. But if you want to get sub categories for given record in user_role, the following will help you

    declare @role_id int = 10
    declare @subCategories nvarchar(max)

    SELECT @subCategories = 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
    where CAT.role_id = @role_id

    SELECT @subCategories as [Sub Categories]

  1. No trackbacks yet.