Need some help with SQL GroupBY

Question

I have a two column table as follows:

ID    Emp ID
1      1
1      2
1      3
1      4
2      2
2      6
2      10
3      1
3      5
4      8
5      2
5      6

I need something like this:

ID   Emp ID
1    1,2,3,4
2    2,6,10
3     1,5
4     8
5     2,6

Please help :)

Answer 1

Depends on your database. You need an aggregation function that concatenates the columns and separates them by columns. This, for example, works in sqlite:

select
    id,
    group_concat(emp_id)
from
    foo
group by id

Answer 2

You may want to look into using the PIVOT statement offered by versions of SQL Server 2005+

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx