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