retrive comma seperated values for matching record in sql query

Question

I have a table called SerialNos with the following columns: Id, Parent_Id, SerialNo

There are different SerialNo on a Parent_Id Like:

Id     Parent_Id    SerialNo       
1          16          abc              
2          16          def             
3          23          hij              
4          23          klm    
5          23          nop

I just want to retrieve comma separated SerialNos. for particular Parent_Id e.g. If Parent_Id is passed 16,then O/p should be : 'abc,def' and if Parent_Id is passed 23,then O/p should be : 'hij,klm,nop'

possible duplicate of [Concatenate values based on ID](http://stackoverflow.com/questions/6603319/concatenate-values-based-on-id) — Mikael Eriksson, Mar 06 '12 at 10:18

score 1 · Answer 1 · answered Mar 06 '12 at 10:15

1

Depending upon the database server you can use group_concat. For example if you are using mysql group_concat with a separator of "," will give you the result you are after. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

If you are using a different database server check its documentation it is likely that it will have something similar. Please also keep in mind that some databases don't have this function.

Sorry just saw that your post was tagged sql-server2008. If sql server 2008 does not have group_concat or something similar try this link http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html

Sorry yet another edit, this will help. http://groupconcat.codeplex.com/

answered Mar 06 '12 at 10:15

Orlymee

2,349
1
22
24

-1 for answering a question that has been asked many times before – gbn Mar 06 '12 at 10:21
Sorry I always consider it to be the responsibility of the person asking the question to check stackoverflow before asking the question! – Orlymee Mar 06 '12 at 10:28
+1 because this answer doesn't meet the criteria to deserve a downvote. "Use your downvotes whenever you encounter an egregiously sloppy, no-effort-expended post, or an answer that is clearly and perhaps dangerously incorrect." – Cameron Mar 06 '12 at 10:44

score 1 · Answer 2 · answered Mar 06 '12 at 10:23

for sql-server 2008 you can use the following query(with your table name):

select distinct STUFF(ISNULL((SELECT ', ' + x.SerialNo
                FROM TableA x
               WHERE x.Parent_Id= t.Parent_Id
            GROUP BY x.SerialNo
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '')
from TableA t
where Parent_Id = 16

score 1 · Accepted Answer · answered Mar 06 '12 at 10:24

1

here is a nice hack:

DECLARE @csv varchar(1000)
SELECT @csv = COALESCE(@csv+',','') + SerialNo
FROM SerialNos
WHERE Parent_Id = 23
SELECT @csv

answered Mar 06 '12 at 10:24

Cameron

1,675
11
12

what about the trailing comma you add? – gbn Mar 06 '12 at 10:27

score 1 · Answer 4 · edited Jul 29 '20 at 19:20

Well i could advice my blog post here, but unfortunately its not in English :). Here is something similar, just without CLR. There i am aware of at least 4 solutions:

CLR grouping function (well, code is in english)
Can use cursors (not very fast)
Can use something similar to cursors
Can use XML

So one solution (not the fastest, but easy to write):

Create Function fn_MyFunction
(
    @Parent_Id int
)
Returns NVarChar(1000)
As
Begin
    Declare @txt NVarChar(1000)
      
    SELECT @txt = COALESCE(@txt + ';' + SerialNo, SerialNo)
    FROM dbo.Table Where Parent_Id = @Parent_Id
    Return @txt
End

retrive comma seperated values for matching record in sql query

4 Answers4