Get all Rows from QUERY() into one cell in a Google Spreadsheet

Question

I have the following formula in a spreadsheet:

=IFERROR( JOIN( CHAR(10), QUERY(Schedule!$A$2:$E, "SELECT C, D, E Where A = '" & B$2 & "' AND B = timeofday '" & text($A4, "HH:MM:SS") & "' AND C = '" & $C$1 & "'", -1)),)

It works great when it returns one row. It does not work if it returns multiple rows. How can I get it to use all rows returned with a CHAR(10) included between rows as well as columns. Basically I want all the returned data separated by line breaks. So when the QUERY returns

Item1 Item2 Item3

Item1A Item2A Item3A

it is changed to

Item1

Item2

Item3

Item1A

Item2A

Item3A

My goal is to put all the returned data in 1 cell with a line break between each item. Any way I can make this happen?

Here is a copy of the file. Cells B3 to F4 are good sample cells where the formula reside.

Answer 1

The solution is imperfect, it adds extra spaces before each line:

  Item1 Item2 Item3
  Item1A Item2A Item3A
^ spaces

Step1

Get origilal formula + extra column in query:

=QUERY(data, "SELECT C, D, E, '@@' Where" & ... & "' label '@@' ''", -1)

The result:

  Item1  Item2  Item3  @@
  Item1A Item2A Item3A @@

Step2

Replace "@@" with char(10)

=ARRAYFORMULA(SUBSTITUTE(step1Formula,"@@",char(10)))

  Item1  Item2   Item3 

  Item1A Item2A  Item3A
                          ^ new lines

Step3

Use concatenate:

=ArrayFormula(" "&TRIM(CONCATENATE(" "&B16:E17)))

Final formula

=ArrayFormula(" "&TRIM(CONCATENATE(" "&SUBSTITUTE(QUERY(Schedule!$A$2:$E, "SELECT C, D, E, '@@' Where A = '" & B$2 & "' AND B = timeofday '" & text($A3, "HH:MM:SS") & IF(OR($C$1 = "*",ISBLANK($C$1)), "", "' AND C = '" & $C$1) & IF(OR($F$1 = "*",ISBLANK($F$1)), "", "' AND E = '" & $F$1) & "' label '@@' ''", -1),"@@",char(10)))))

Shared file:

https://docs.google.com/spreadsheets/d/1otcqvkXb5H3WTSi_exKw9UUcRqN8MKBZ5JYCmTLAWdQ/edit?usp=sharing

Answer 2

I know this is not an elegant solution, but it will help you in your case. Enter this formula at B3 and then copy and paste it onto other cells.

=iferror(join(char(10), query(Schedule!$A$2:$E, "select C,D,E where A='"&B$2&"' and B=timeofday '"&text($A3, "hh:mm:ss")&"' limit 1 offset 0")),"")
&iferror(char(10)&char(10) & join(char(10), query(Schedule!$A$2:$E, "select C,D,E where A='"&B$2&"' and B=timeofday '"&text($A3, "hh:mm:ss")&"' limit 1 offset 1")),"")
&iferror(char(10)&char(10) & join(char(10), query(Schedule!$A$2:$E, "select C,D,E where A='"&B$2&"' and B=timeofday '"&text($A3, "hh:mm:ss")&"' limit 1 offset 2")),"")
&iferror(char(10)&char(10) & join(char(10), query(Schedule!$A$2:$E, "select C,D,E where A='"&B$2&"' and B=timeofday '"&text($A3, "hh:mm:ss")&"' limit 1 offset 3")),"")
&iferror(char(10)&char(10) & join(char(10), query(Schedule!$A$2:$E, "select C,D,E where A='"&B$2&"' and B=timeofday '"&text($A3, "hh:mm:ss")&"' limit 1 offset 4")),"")

Basically you cannot concat result strings with google query(), so instead I'd fetch one row at a time using limit 1 offset 0, limit 1 offset 1, limit 1 offset 2, and so on. Maximum is 5 in this case. Sample sheet is here.