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

NOTE: The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.

See GROUP_CONCAT for more details and how to change max length.

Answer 2

I know that in MySQL there is GROUP_CONCAT and in Sybase I think it's LIST as stated in another answer:

SELECT id, LIST(data||', ')
FROM yourtable
GROUP BY id

Answer 3

For PostgreSQL, using a similar function string_agg.

SELECT id, string_agg(data, ',')
FROM yourtable
GROUP BY id

Answer 4

In PL/SQL you can do it by:

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

Answer 5

In MsSQL you can use a function (Don't know if there is somenthing similar in SyBase)

CREATE FUNCTION [dbo].[GetDataForID]
(
    @ID int
)
RETURNS varchar(max)
AS
BEGIN
    declare @output varchar(max)
    select @output = COALESCE(@output + ', ', '') + data
    from table
    where ID = @ID

    return @output
END
GO

And then:

SELECT ID, dbo.GetDataForID(ID) as Data
FROM Table
GROUP BY ID

Answer 6

For SQL server:

SELECT id, STRING_AGG(data, ',')
FROM your_table
GROUP BY id;

Answer 7

You can't do this in a straight GROUP BY in plain-vanilla SQL. You have to use a cursor (or a similar construct) to concatenate the values in each group manually.

• Oracle lets you define a custom aggregator which would do this concatenation in PL/SQL.
• SQL Server lets you define a custom aggregator in .NET which would do this, as well.
• I'm not sure about what options Sybase has for defining custom aggregators, though.

Answer 8

For Presto, use array_agg

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

Answer 9

Try this one:

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

Answer 10

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 11

In SQL Server:

select distinct b.id, data=STUFF((select ',' +convert(varchar,id)
from yourtable a
where a.id=b.id
for xml path ('')),1,1,'')
from yourtable b

Answer 12

It's been a few years since I've tried using the syntax and I no longer have access to an iAnywhere instance, but there was an aggregate function (list) to do such a task. I cannot confirm if LIST() is still supported.

SELECT id,
       LIST(data)
FROM   table
GROUP BY id

Answer 13

For SparkSQL (e.g. when querying AWS Athena):

SELECT id, ARRAY_JOIN(ARRAY_AGG(data), ',')
FROM your_table
GROUP BY id;