How to get list of values in GROUP_BY clause?

Question

If I have data like this in a table

id   data
--   ----
1    1
1    2
1    3
2    4
2    5
3    6
3    4

How do I get results like this in a query (on sybase server)?

id   data
--   ----
1    1, 2, 3
2    4, 5
3    6, 4

Answer 1

In mysql, use

SELECT id, GROUP_CONCAT(data)
 FROM yourtable
 GROUP BY id

or use your custom separator:

SELECT id, GROUP_CONCAT(data SEPARATOR ', ')
 FROM yourtable
 GROUP BY id

see GROUP_CONCAT.

Answer 2

I think you're going to have to use a cursor (http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/50501;pt=50305)

Answer 3

In PL/SQL you can do it by:

SELECT id, LISTAGG(data, ',') WITHIN GROUP(ORDER BY 0) "data"
  FROM yourtable
 GROUP BY id