Tuesday, October 02, 2007 #

T-SQL User defined function to concatenate column to CSV string

I had a  complicate (and buggy) T-SQLstored procedure, that concatenate fields from details table and show them as one field in the resultset of the main table(Itinerary and summary of itinerary items. I've looked in pivot functionality, but didn't fint how to make it working in the way I needed. Furthermore, Pivot is available for SQL Server DB with compatibility level 90(aka SQL server 2005), but our db still has compatibility level 80(aka SQL server 2000). 
So I desided to write UDF to concatenate column values into CSV string for each row.
UPDATE: See below simpler alternative using FOR XML.
Thanks to the discussion I've created UDF similar to the following:

Alter FUNCTION Components_ProvidersCsvForItinerary

(

      -- Add the parameters for the function here

      @ItineraryId int

)

RETURNS nvarchar(3000)

AS

BEGIN

      -- Declare the return variable here

declare @keywords nvarchar(3000)

 

 

--Build your csv string of keywords

Select @keywords = null

SELECT @Keywords = Coalesce(@Keywords + ', ', '') +

IC.ProviderCode

from ProviderComponents IC

where IC.ItineraryID =@ItineraryID

 

--- Return the result of the function

      RETURN @keywords

/*

select dbo.Components_ProvidersCsvForItinerary (234563)

select top 10 ItineraryID, Replace(dbo.Components_ProvidersCsvForItinerary (ItineraryID),',','/')

 from ItineraryTable

order by ItineraryID Desc

*/

END

 

 
Unfortunately, you can't make it generic(tables and columns are different for each task), but it can be implemented as template.
May be Table variables and temporary tables(see original  discussion) have better performance, that UDF( I didn't test), but the UDF makes the code better structured and easier to read/understand. 
Note thar there are a lot of warnings about BAD User Defined Scalar Functions performance:
 User Defined Function performance comparison
Comment here said, that table-valued functions join with other tables in the FROM clause, much like I would have used a view, is OK. However, using a UDF in the WHERE or SELECT clause would cause serious performance issues.
UPDATE: Thanks to Gerry for providing simpler alternative:.
SELECT STUFF((SELECT ', ' + Table.ColumnName FROM Table FOR XML PATH('')),1, 2, '') AS CSVColumn
This will select your ColumnName from Table as a CSV string.
Note that STUFF is required just to remove leading comma. I didn't check which method has better performance.
By the way, I have similar function in ADO.Net - see TableColumnToString(DataTable tbl, string sColumnName, string delimeter) in My DataHelper class 
posted @ Tuesday, October 02, 2007 3:18 PM | Feedback (8)
«October»
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

About
Article Categories
Archives
Post Categories
Links
Syndication:

RSS ATOM