How to get list of values in GROUP_BY clause?
SqlGroup BySybaseSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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.
Solution 3 - Sql
For PostgreSQL, using a similar function string_agg.
SELECT id, string_agg(data, ',')
FROM yourtable
GROUP BY id
Solution 4 - Sql
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
Solution 5 - Sql
In PL/SQL you can do it by:
SELECT id, LISTAGG(data, ',') WITHIN GROUP(ORDER BY 0) "data"
FROM yourtable
GROUP BY id
Solution 6 - Sql
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.
Solution 7 - Sql
For SQL server:
SELECT id, STRING_AGG(data, ',')
FROM your_table
GROUP BY id;
Solution 8 - Sql
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)
Solution 9 - Sql
Try this one:
SELECT id,
GROUP_CONCAT(data)
FROM table
GROUP BY id
Solution 10 - Sql
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
Solution 11 - Sql
For Presto, use array_agg
SELECT id, array_agg(data)
FROM yourtable
GROUP BY id
Solution 12 - Sql
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
Solution 13 - Sql
For SparkSQL (e.g. when querying AWS Athena):
SELECT id, ARRAY_JOIN(ARRAY_AGG(data), ',')
FROM your_table
GROUP BY id;