How to use CONCAT in QUERY?
There is no concat
option in Google Sheets query
.
Workaround, suggested by JPV may fit you: https://stackoverflow.com/a/29799384/5372400
use formula:
=ARRAYFORMULA(
SUBSTITUTE(
trim(transpose(query(transpose(_your_query_here_),,COLUMNS(_your_query_here_))))," "," → ")
)
Change " → "
to space " "
to concat the result with space.
More info about SQL in Sheets:
https://developers.google.com/chart/interactive/docs/querylanguage
Use Transpose twice.
=transpose(query(transpose(_you query_);;COLUMNS(_you query_)))
For an example from my question:
=transpose(query(transpose(UNIQUE(query(A:C; "select * where A contains 'BEN' OR A contains 'ANTON' order by A")));;COLUMNS(UNIQUE(query(A:C; "select * where A contains 'BEN' OR A contains 'ANTON' order by A")))))
Result:
ANTON IRKUTSK NO
ANTON TORONTO NO
BEN MOSCOW YES
BEN PARIS YES
BEN MOSKOW NO