SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



SQL Concatenation - Get column values as comma seperated list using XML PATH() instead of UDF's using SQL COALESCE

In my SQL Server projects I frequently need sql string concatenation function to get a list of column values of a table which may be called as child just as a column of a parent record which can be simply solved as sql concatenation methods.
In this case it is possible to solve this t-sql concatenate problem by creating a udf (Used Defined Function).
We can select the parent rows, and as an additional column in the select we can call the udf concatenation function which gathers and concatenate string in sql the child values as comma or semicolon seperated scalar value.
I have created many sql concatenation udf's in order to help in different situations and conditions which all are using the T-SQL COALESCE function in its sql source codes.

Below developers can find a very simple T-SQL sample using FOR XML PATH() in order to sql concatenate strings values as a sample sql concatenation in MSSQL.

SELECT
  STUFF(
    (
    SELECT
      ' ' + Description
    FROM dbo.Brands
    FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'
    ), 1, 1, ''
  ) As concatenated_string

Thanks to Harris, he informed me about the previous solution doesn't take care of characters like "&", ">" or "<".
He let me correct this missing point by using ".value" with XML PATH syntax.

Here is an other sample user defined function which may help you in sql concatenate strings:

ALTER FUNCTION dbo.GetServiceFunctionsList
(
  @ServiceId int
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @ReturnValue nvarchar(max)

SELECT @ReturnValue = COALESCE(@ReturnValue, '') + [Description] + ','
FROM ServiceServiceFunctions SSF
INNER JOIN ServiceFunctions SF on SF.ServiceFunctionId = SSF.ServiceFunctionId
WHERE SSF.serviceid = @ServiceId

RETURN SUBSTRING(@ReturnValue, 1, LEN(@ReturnValue)-1)
END
GO

But there is a better sql string concatenation approach which eliminates the use of udf's and variables.
This method uses the XML PATH as the key for solving the concatenation problem.

Run the below SQL queries in order to see how the output changes:

SELECT [description]
FROM ServiceServiceFunctions ssf
INNER JOIN ServiceFunctions sf on sf.ServiceFunctionId = ssf.ServiceFunctionId
WHERE sf.ServiceFunctionId = ssf.ServiceFunctionId
AND ssf.serviceid = 1
FOR XML PATH

SQL query output is as below:

<row>
<description>FullService</description>
</row>
<row>
<description>Function 2</description>
</row>

The returned result is in form of XML fragment and includes row and description tags

SELECT [description]
FROM ServiceServiceFunctions ssf
INNER JOIN ServiceFunctions sf on sf.ServiceFunctionId = ssf.ServiceFunctionId
WHERE sf.ServiceFunctionId = ssf.ServiceFunctionId
AND ssf.serviceid = 1
FOR XML PATH('')

Now the output is changed as follows

<description>FullService</description> <description>Function 2</description>

This time I have changed the FOR XML PATH as FOR XML PATH('') which eliminated the row tags from the resultant XML document fragment.

SELECT ',' + [description]
FROM ServiceServiceFunctions ssf
INNER JOIN ServiceFunctions sf ON sf.ServiceFunctionId = ssf.ServiceFunctionId
WHERE sf.ServiceFunctionId = ssf.ServiceFunctionId
AND ssf.serviceid = 1
FOR XML PATH('')

,FullService,Function 2

This time I have added the comma (',') which causes the result set have returned column without a column name.
This is very important since the result of the above query is now a string value and since we used the comma we have now comma seperated values which results as a transact sql concatenate strings.

Furthermore for a better string concatenate in sql, we can delete or remove the comma using the STUFF function to replace the first character in the string values with an empty string value.

SELECT STUFF(',FullService,Function 2', 1, 1, '')

will return "FullService,Function 2" which also helped us instead of using SUBSTRING() and LEN() functions.

SELECT
  S.ServiceId,
  STUFF(
    (
      SELECT ',' + [Description]
      FROM ServiceServiceFunctions SSF
      INNER JOIN ServiceFunctions SF
        ON SF.ServiceFunctionId = SSF.ServiceFunctionId
      WHERE SF.ServiceFunctionId = SSF.ServiceFunctionId
      AND SSF.Serviceid = S.Serviceid
      FOR XML PATH(''),TYPE
      ).value('.','VARCHAR(MAX)'
    ), 1, 1, '') as functions
FROM Services S

The above Transact SQL concatenate query is the way to sql concatenate strings columns into one value using the XML PATH()






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems