Reference a cell to populate an array with its value with Google Sheets QUERY function

Question

I am trying to put together a QUERY that I need to reference a static cell within the SELECT clause to input the cell's value as column value.

I've browsed around Stack Overflow and could not find that specific issue being referred to.

I need a workaround for:

= QUERY(Data!A1:R, 
         "SELECT A, Sheet2!B1, R 
          WHERE A is not null
          LABEL Sheet2!A1 'Batch ID',1)

And it should be resulting in:

+--------------------------+
|       QUERY output       |
+--------------------------+
| Name | Batch ID | Status |
+------+----------+--------+
| Alan | 7632r    | Sent   |
+------+----------+--------+
| Joe  | 7632r    | Sent   |
+------+----------+--------+

And the static reference cell from Sheet2!B1:

+------------------+
| Sheet2 reference |
+------------------+
| Batch ID | 7632r |
+----------+-------+

Apologies for not sharing a dummy sheet, but I have corporate security restrictions, that are not allowing me to share any sheet's link to anyone outside the company.

I am also interested in what downsides are possible if this can be made to work?

Answer 1

pausing query argument with '" appending cell with & then again appending the continue of query argument & and reopening argument with "'

=QUERY(Data!A1:R, 
 "select A, '"&Sheet2!B1&"', R 
  where A is not null
  label '"&Sheet2!B1&"' 'Batch ID', 1)