Google Sheets "=QUERY()" JOIN ON or equitant-CodePudding

I have one large spreadsheet with names, addresses, phone numbers, emails, Etc. Some records have a second address for which I have a column named "Address 2" I was hopping to write a query that would give me an output with duplicate rows of which the only difference was the "Address 2" column would be in the main address Column.

Data:

	A	B	C	D	E	F	G
1	Status	Name	Address	Phone	Email	Address2	Hire Date
2		Joe Smith	123 Smith St	201 555 3099	[email protected]	7th Avenue Sq
4	Q	Jane Smith	321 Not Smith St				12/15/1980
5		Robert Smith		818 555 4321	[email protected]		12/13/1981

Looking for an Query output to look like:

	A	B	C	D	E	F
1	Status	Name	Address	Phone	Email	Hire Date
2		Joe Smith	123 Smith St	201 555 3099	[email protected]
3		Joe Smith	7th Avenue Sq	201 555 3099	[email protected]
4	Q	Jane Smith	321 Not Smith St			12/15/1980
5		Robert Smith		818 555 4321	[email protected]	12/13/1981

I was trying something like:
=QUERY({Sheet1!$A2:$G,Sheet1!$B2:$B,Sheet1!$F2:$J },"SELECT Col1, Col2, Col3, Col4, Col5, Col7 JOIN Col6 ON Col2 = Col2")

Which I think is more or less how it would be in SQL, but Google sheets doesn't have a join function.

Is there any way to get this done?

CodePudding user response：

most simple you can do is:

=QUERY({A1:E, G1:G; A2:B, F2:F, D2:E, G2:G}, "where Col3 is not null", )

CodePudding user response：

Something like this? You can stack data of the same length with {}, this sample create 2 query function and stack them together.

=ArrayFormula(
LAMBDA(DATA_1,DATA_2,
 QUERY({DATA_1;DATA_2},"WHERE Col2 IS NOT NULL ORDER BY Col2",1)
)(
 QUERY({A1:G4},"SELECT "&JOIN(",","Col"&{1,2,3,4,5,7}),1),
 QUERY({A1:G4},"SELECT "&JOIN(",","Col"&{1,2,6,4,5,7})&" WHERE Col6 IS NOT NULL LABEL "&JOIN(",","Col"&{1,2,6,4,5,7}&"''"),1)
)
)