CODEWITHSUNDEEP
mysqlsql
Chika
Chika

Reputation: 25

ANSI aggregate functions for SQL

Looking at ANSI aggregate functions for SQL and I can't find anything for strings. However each database seems to have its own, e.g GROUP_CONCAT and LISTAGG for MySQL and Oracle respectively, making portability a little difficult. Is there something I am missing? Is there a reason for this?

Upvotes: 0

Views: 1346

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109146

An aggregate function for this is specified in the standard, LISTAGG; it was specified in SQL:2016 (ISO-9075-2:2016) in section 10.9 <aggregate function>. The fact each database has its own, is because it wasn't standardized in earlier versions of the standard.

Why it wasn't standardized before would be guessing to the reasons, deliberations and arguments of the standardization committee, which - as far as I know - are not publicly available, but either it wasn't considered important enough, or the committee couldn't come to an agreement on syntax and behaviour in earlier versions.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

ANSI has adopted listagg() as the standard. I would not hold my breath waiting for other databases to change their function, though.

String aggregation was either viewed as unimportant originally or the committee could not decide on an appropriate standard.

Here is an interesting perspective on the issue regarding Postgres. I would caution reading too much into Oracle controlling the standards committee (unless the author has inside information). IBM has also been very active and DB2 supports listagg().

Upvotes: 2

Related Questions