During my work I had encountered a situation where I need to get a comma separated list as an output from the values in a SQL query result. To elaborate it more, my query returns set of records (multiple records which match a certain criteria) as its result. Then there’s a column which I need to take as a comma separated value set.
For example, let’s imagine my query result is like this;
So, the output which I required is something like this;
John, Kumar, Kalana, Chamath, Mark
The solution I have came up with is the below piece of code.
CREATE FUNCTION [dbo].[fnGetStudentList] ( ) RETURNS NVARCHAR (1000) AS BEGIN DECLARE @StdList NVARCHAR (1000) -- Variable to hold the comma separated list SET @StdList = '' SELECT @StdList = @StdList + CASE WHEN (@StdList = '') THEN '' ELSE ', ' END + StdName FROM ( SELECT StudentName AS StdName FROM MyTestDB.dbo.Students ) RETURN @StdList END
Actually, in my real scenario, it had to output a data table. In that some columns need to contain comma separated values which depends on a value(s) contained in another column(s). Therefore I created this as an SQL function which can be called in a select query, and can be re-used.
Later, I have done a slight modification to the query by removing the CASE statement. Modified code part is as below.
SELECT @StdList = COALESCE(@StdList+', ' ,' ') + StdName FROM ( -- Select query goes here )
COALESCE method in T-SQL returns the first non-null expression among its arguments.
No comments:
Post a Comment
Had to enable word verification due to number of spam comments received. Sorry for the inconvenience caused.