google sheetsgoogle-sheets-query
I have a query that is really long (about 50 columns), I don't want to split into 2 queries. But I want a column to show up twice. Is there a way to do this? below is a simple example that doesn't work.
How to select B twice? The ability to select something twice is pretty standard in any SQL right?
=query(B3:D7,"select B,C,B",0)
The following formula will display the corresponding teachers.
=ARRAYFORMULA(array_expression)
=VLOOKUP(search_criterion, array, index, sort_order)
=IF(test, then_value, otherwise_value)
=SIGN(number)
=ROW(reference)
=ARRAYFORMULA( // ARRAYFORMULA start
VLOOKUP( // VLOOKUP start
B2:B, // search_criterion
DATA!A2:G, // array
// index start
IF( // IF start
C2:C="1st", // test
{4,5}, // then_value
{6,7} // otherwise_value
) * // IF end
SIGN( // SIGN start
ROW( // ROW start
A2:A // reference
) // ROW end
), // SIGN end
// index end
0 // sort_order
) // VLOOKUP end
) // ARRAYFORMULA end
// to copy / paste
=ARRAYFORMULA(VLOOKUP(B2:B,DATA!A2:G,IF(C2:C="1st",{4,5},{6,7})*SIGN(ROW(A2:A)),0))
The SIGN and the ROW function are there to meet up with the criteria, set with using an ARRAYFORMULA. It will return an equally long array as setout in the search_condition. The IF function sets the condition for the index, by using the test.
I've copied your file and added my solution to it: teacher references
Try the following modification.
=QUERY(DATA, "SELECT B WHERE A='Exit' AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' ")
Best Answer
According to Google Help, the selected column must be an identifier, so it seems select a column twice is not possible within google query language.
Source: https://developers.google.com/chart/interactive/docs/querylanguage#select
However, if your query is not too complex, you can cheat using aggreagation operators.
If your column B is a number:
=query(B3:D7,"select B,C,B+0",0)If your column B is a string:
There is no neutral operator, but upper or lower can be usefull if the case isn't an issue for you.
=query(B3:D7,"select B,C,UPPER(B)",0)If you use label, you'll have to respect the same identifier syntax used in your select clause.