How to concatenate columns in a query

• Introduction
• Method

How to concatenate columns in a query and return the results.

Introduction

If you want to produce aggregated columns in your query results (like concatenated columns), then you can do this without needing to add extra columns!

Method

By using the arrayformula function in conjunction with concatenate you can supply concatenated columns into your query formulas.

{ARRAYFORMULA(A2:A&", "&B2:B&" ("&C2:C&")"),K2:K}

The formula above creates a two-column list, with the first column being a concatenation of columns A, B and C. This is useful if you have names and a group/identifier in these columns. If column A contains a surname (Doe), column B a given name (John) and column C a form group (9A) then then concatenated column will produce: “Doe, John (9A)”. Column K in this example might be a status column or a value upon which you want to select, e.g. show all the people who are going on a trip.

IFERROR(QUERY({ARRAYFORMULA(A2:A&", "&B2:B&" ("&C2:C&")"),K2:K}, "select Col1 where Col2 = TRUE or Col2 = 'TRUE'", 0),)

This data set can then be fed into a normal query function, to produce a filtered set of results, returning just the concatenated column! The outer iferror function is used to catch the condition where no rows are returned, showing a blank rather than an error.