Monday, July 21, 2008

Turning Tables into Delimited Text

The world isn’t relational. It’s not divided into rows and columns. Sometimes
when there’s a list associated with a row, it’s better to show the list
as comma-separated text rather than try to preserve a relational presentation.
I use this technique on reports and sometimes on online grids. The
UDFs that implement this technique are not multistatement but scalar.
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

1 comment:

AndrewLeeUK said...

Stumbled across this page whilst searching for something else.
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