How to use CONCAT in QUERY?

Question

I have a table:

 A     |   B      |     C
BEN    |  MOSKOW  |    YES
ANTON  |  IRKUTSK |     NO
VIKTOR |  PARIS   |    YES
BEN    |  PARIS   |    YES
ANTON  |  TORONTO |     NO
DON    | TORONTO  |    YES
ANNA   | IRKUTSK  |    YES
BEN    |  MOSKOW  |     NO

and tried a formula:

=UNIQUE(query(A:C; "Select A,B,C where A contains 'BEN' OR A contains 'ANTON' order by A"))

and an arrayformula:

=arrayformula(UNIQUE(query(A:C; "Select A,B,C where A contains 'BEN' OR A contains 'ANTON' order by A")))

but the results are in columns:

ANTON  |  IRKUTSK |     NO
ANTON  |  TORONTO |     NO
BEN    |  MOSKOW  |    YES
BEN    |  PARIS   |    YES
BEN    |  MOSKOW  |     NO

whereas I need results in only one cell per row, like so:

ANTON IRKUTSK NO
ANTON TORONTO NO
BEN MOSCOW YES
BEN PARIS YES
BEN MOSKOW NO

Pichardo

u can use CONCATENATE(A," ", B) – Benzyl

=UNIQUE(query(A:C; "Select CONCATENATE(A," ", B," ", C) where A contains 'BEN' OR A contains 'ANTON' order by A")) not work – Corned

There is CONCAT in sheets, but not in Google Query, which is what the question is – Drilling

Answer 1

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

Pichardo

this is a super handy trick, thank you – Boggs

Answer 2

There is no concat option in Google Sheets query.

Workaround, suggested by JPV may fit you: https://mcmap.net/q/829294/-google-sheet-arrayformula-join-and-split-functions

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

Hagan