Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Non grouping version of LIST function [CORE4335] #4658

Open
firebird-automations opened this issue Feb 12, 2014 · 7 comments
Open

Non grouping version of LIST function [CORE4335] #4658

firebird-automations opened this issue Feb 12, 2014 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Tim Kelly (m00bh000)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Tim Kelly (m00bh000)

description: 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-grouping version of LIST function, say STRINGLIST this would basically 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
---

=>

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-grouping version of LIST function, say STRINGLIST this would basically 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
---

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 the 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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Tim Kelly (m00bh000)

description: 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-grouping version of LIST function, say STRINGLIST this would basically 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
---

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 the 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.

=>

The aggregate LIST function is very useful however 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-grouping version of LIST function, say STRINGLIST this would basically 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
---

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 the 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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Tim Kelly (m00bh000)

description: The aggregate LIST function is very useful however 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-grouping version of LIST function, say STRINGLIST this would basically 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
---

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 the 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.

=>

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
---

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Tim Kelly (m00bh000)

description: 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
---

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.

=>

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Why not to create a user-defined function FORMAT_ADDRESS(LINE1, LINE2, CITY, ZIPCODE)?

@firebird-automations
Copy link
Collaborator Author

Commented by: Tim Kelly (m00bh000)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Tim Kelly (m00bh000)

Maybe LISTVALUE & AVGVALUE would be more in keeping with the naming of the other functions.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant