Geeks With Blogs

Michael Freidgeim's Blog MS .Net Development
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: Thanks to Gerry for providing simpler alternative using FOR XML:.
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.
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.

By the way, I have similar function in ADO.Net - see TableColumnToString(DataTable tbl, string sColumnName, string delimeter) in My DataHelper class 


If you need an opposite operation- to Split CSV string into the table (e.g. to use in IN clause) - see
Passing CSV to Stored Procedure or
 A SQL 2005 Managed CSV Split UDF 

Posted on Tuesday, October 2, 2007 3:18 PM | Back to top


Comments on this post: T-SQL User defined function to concatenate column to CSV string

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Excellent! saved me mucho time.
Left by howie on Nov 09, 2007 3:33 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Many thanks for providing this code. I have searched it for a long time. I can immediately apply it to my project

Would you be so kind to modify your code so that it only concatenate distinct ProviderComponents.ProviderID

Thanks
Bing from Germany
Left by bing on Feb 24, 2008 5:29 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Should be something like
SELECT @Keywords = Coalesce(@Keywords + ', ', '') +
IC.ProviderCode
from ProviderComponents IC
where IC.ProviderID in (Select distinct ProviderComponents.ProviderID from ProviderComponents)

I haven't tested the SQL.
Left by Michael Freidgeim on Feb 24, 2008 9:34 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
declare @strResult nvarchar(3000)
set @strResult = null

SELECT @strResult = Coalesce(rtrim(ltrim(@strResult)) + ', ', '') + UserName
from
(select distinct UserName from dbo.tblUser) T

RETURN @strResult
Left by itay on Mar 20, 2008 2:39 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
I used to use the coalesce technique for this until i recently stumbled accross this.
Check it out:

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.

sneaky huh,
Left by Gerry on May 09, 2008 6:43 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Thanks, Gerry!
It's very cool.
Left by Michael Freidgeim on May 09, 2008 12:59 PM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Fantastic stuff, I applied it to my project and it works perfectly, Thank you!!!
Left by Ally on Sep 08, 2008 12:33 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Gerry, awesome man.
You saved my life!
Left by Justin on Sep 07, 2009 10:16 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Is there an SQL 2000 version of Gerry's approach?
Left by Dooza on Oct 05, 2009 6:19 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Gerry,

One thing to watch out for with that approach. For XML will convert some characters to XML. For instance, if your data contains an '&' character, the concatenated results will have '&' instead. I typically handle this scenario on a case by case basis with a REPLACE function.
Left by Markos on Nov 13, 2009 3:40 PM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Another great alternative to create csv string from column data
Left by Ranu Mandan on May 25, 2011 6:05 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Gray

you are great!
it's very helpful
Left by su on Jul 15, 2011 5:25 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
ts, this just save me lots of work
Left by MIke on Dec 13, 2011 3:36 AM

# re: T-SQL User defined function to concatenate column to CSV string
Requesting Gravatar...
Nice One Really Helped Me!!!!!!! Cheers
Left by Vinod R on May 21, 2014 8:50 PM

Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net | Join free