Get column values as comma seperated list using XML PATH() instead of UDF's using COALESCE
In my projects I frequently need to get a list of column values of a table which may be called as child just as a column of a parent record.
In this case it is possible to solve this t-sql 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 function which gathers and concatenates the child values as comma or semicolon seperated scalar value.
I have created many udf's in order to help in different situations and conditions which all are using the COALESCE function in its sql source codes.
Here is a sample user defined function which may help you in such cases:
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 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 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
<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('')
<description>FullService</description>
<description>Function 2</description>
This time I have changed the PATH as 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.
Furthermore 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('')
), 1, 1, '') as functions
FROM Services S
The above query is the way of concatenating columns into one value using the XML PATH()
 BlinkList
 Del.icio.us
 Digg
 Furl
 Simpy
 Spurl
 DZone
 ma.gnolia
 Shadows
|