Turning Tables into Delimited Text
- Get link
- X
- Other Apps
Creating a Comma-Separated List of Authors for a Title in SQL Server
In certain scenarios, representing data in a relational format might not be the most intuitive choice. For instance, when dealing with lists associated with rows, displaying them as comma-separated text can be more practical, especially for reports and online grids. In this blog post, I’ll share a scalar user-defined function (UDF) that implements this technique.
The udf_Titles_AuthorList
Function
CREATE FUNCTION udf_Titles_AuthorList (
@title_id char(6) -- title ID from pubs database
) RETURNS varchar(255) -- List of authors
-- No SCHEMABINDING reads data from another DB
/*
* Returns a comma-separated list of the last name of all
* authors for a title.
*
* Example:
* Select Title, dbo.udf_Titles_AuthorList(title_id) as [Authors]
* FROM pubs..titles ORDER by Title
****************************************************************/
AS BEGIN
DECLARE @lname varchar(40) -- one last name.
, @sList varchar(255) -- working list
SET @sList = ''
DECLARE BookAuthors CURSOR FAST_FORWARD FOR
SELECT au_lname
FROM pubs..Authors A
INNER JOIN pubs..titleAuthor ta
ON A.au_id = ta.au_id
WHERE ta.title_ID = @Title_ID
ORDER BY au_lname
OPEN BookAuthors
FETCH BookAuthors INTO @lname
WHILE @@Fetch_status = 0 BEGIN
SET @sList = CASE WHEN LEN(@sList) > 0
THEN @sList + ', ' + @lname
ELSE @lname
END
FETCH BookAuthors INTO @lname
END
CLOSE BookAuthors
DEALLOCATE BookAuthors
RETURN @sList
END
This user-defined function, udf_Titles_AuthorList
, takes a title ID as input and returns a comma-separated list of the last names of all authors associated with that title. Feel free to use and customize this function to suit your specific needs.
In a blog post, it’s helpful to provide context, explanations, and perhaps examples of how to use the function, which I’ve included in the markdown. Adjust the content based on your blog post structure and additional information you want to provide.
- Get link
- X
- Other Apps
Comments
But a better method for creating a CSV string is something like this.
The big advantage is it doesn't need to use a cursor.
CREATE FUNCTION udf_Titles_AuthorList (
@title_id char(6) -- title ID from pubs database
) RETURNS varchar(max) -- List of authors
declare @sList varchar(max)
set @sList = ''
SELECT @sList = @sList + case len(@sList) when 0 then '' else ',' end + au_lname
FROM pubs..Authors A
INNER JOIN pubs..titleAuthor ta
ON A.au_id = ta.au_id
WHERE ta.title_ID = @Title_ID
ORDER BY au_lname
return @sList
end
Cheers, Andy