Add a constant number to google sheets query result

Question

I have data in two columns labeled Email and 01 respectively.

I want to use a query to rearrange the data so that there are 3 columns:

Columns 2 and 3 will contain the data from my original two columns if the second column contained the word "cool"
Column 1 for each populated row will contain 01

Example Data

The three rows {B6:C7;B10:C10} are included in F6:G8 of the results because they all had "cool" in column C.
The 01 from C5 is repeated in Column E for each populated row in Columns F, G.

How can I perform a query like this?

JohnSUN · Accepted Answer · 2022-09-23 17:56:09Z

={"","email","";QUERY(B6:C,"select '" & TRIM($C$5) &"', B, C where C='cool' label '" & TRIM($C$5) &"' ''",0)}

This formula will literally do what is shown in your screenshot.

Update For second screenshot no need QUERY():

={"","email","";ArrayFormula(SORT(SPLIT(FLATTEN(C5:E5&"🦊"&B6:B10&"🦊"&C6:E10),"🦊"),1,TRUE))}