Consider a database table holding names, with three rows:
Jul 31, 2008 - How do you go about producing a summary result in which a. Here is an example of a string concatenate aggregate function written using C#. Where a table variable with three columns is used within a table-valued UDF.
Is there an easy way to turn this into a single string of
Peter, Paul, Mary
?JohnnyMJohnnyM
45 Answers
prev 12
GraemeGraeme
Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking thru the 30 odd answers I still had a twist on a similar answer already given similar to using COALESCE for a single group list and didn't even have to set my variable (defaults to NULL anyhow) and it assumes all entries in my source data table are non blank:
I am sure COALESCE internally uses the same idea.Lets hope MS don't change this on me.
GlenGlen
Ravi PipaliyaRavi Pipaliya
How about this:
Where the '300' could be any width taking into account the max number of items you think will show up.
Hans BluhHans Bluh
One way you could do it in SQL Server would be to return the table content as XML (for XML raw), convert the result to a string and then replace the tags with ', '.
ManuManu
Priti Getkewar JoshiPriti Getkewar Joshi
With TABLE type it is extremely easy. Let's imagine that your table is called
Students
and it has column name
.This example is tested in MS SQL Server 2008 R2
Max TkachenkoMax Tkachenko
Muhammad BilalMuhammad Bilal
Although it's too late, and already has many solutions. Here is simple solution for MySQL:
ShahbazShahbaz
Below is a simple PL/SQL procedure to implement the given scenario using 'basic loop' and 'rownum'
Table definition
Let's insert values into this table
Procedure starts from here
Result
Pooja BhatPooja Bhat
AuraAura
Esperento57Esperento57
We can use RECUSRSIVITY, WITH CTE, union ALL as follows
Kemal AL GAZZAHKemal AL GAZZAH
Depends on your database vendor. MySQL has concat_ws. MS SQL Server expects you to do it in your client application.
Update: you could also do it in an external procedure or UDF, perhaps by using a cursor or calling out to CLR code.
Joel CoehoornJoel Coehoorn
Hamid BahmanabadyHamid Bahmanabady
protected by Community♦Aug 2 '11 at 14:32
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Would you like to answer one of these unanswered questions instead?