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
The desired output here is to concatenate the subcategories in a single row as:
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
Hey Thanks Buddy…
This is cool! However when I use it the resulting column is a CLOB. Any ideas?
Hi John,
You can cast it explicitly to change resulting data type:
CAST( STUFF……. 1,1, ” ) AS NVARCHAR(500)) AS [Sub Categories]
Really Good information. Thxs Vishal
ple tell me the Table structure on which u apply XML path
one minor observation – I believe a GROUP BY will be required for Category, and perhaps an ORDER BY as well?
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;
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;
IS it possible to do the opposite?
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
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.
Very helpful!!! Thank you!!