Issue Details (XML | Word | Printable)

Key: CORE-4335
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Tim Kelly
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Non grouping version of LIST function

Created: 12/Feb/14 12:40 PM   Updated: 12/Feb/14 05:01 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
The aggregate LIST function is very useful however unfortunately currently there is no non-aggregate version.

I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas.

I propose a new non-aggregate version of the LIST function, say STRINGLIST. This would concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list).

SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE:

----
STRINGLIST
A,B,C
----

SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE

---
STRINGLIST
A,C
---

Example of real-world usage:

SELECT STRINGLIST(ADDRESS_LINE1, ADDRESS_LINE2, CITY, ZIPCODE) FROM...


Currently I do this (which is almost equivalent)

SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE

But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma!

I suggest that any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. An option to remove duplicates would be handy similar to the aggregate version.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 12/Feb/14 02:08 PM
Why not to create a user-defined function FORMAT_ADDRESS(LINE1, LINE2, CITY, ZIPCODE)?

Tim Kelly added a comment - 12/Feb/14 04:57 PM
The address was just an example, a general purpose function to comma separate concatenated expressions would be really helpful.

Yes I could get round to writing a UDF but I still think this is useful enough to be built in.

There are non-aggregate versions of the other aggregate functions eg MIN -> MINVALUE, MAX -> MAXVALUE, SUM -> +, It seems to me there should be one for LIST, oh and AVG too.

Tim Kelly added a comment - 12/Feb/14 05:01 PM
Maybe LISTVALUE & AVGVALUE would be more in keeping with the naming of the other functions.