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()