How to prefix all columns in a join table withe the names of their origin table without explicitly renaming them one by one?

Question

I am joining a few tables which have many columns and also have duplicate column names. To remember which column came from which table, I would like to prefix/suffix all columns with the table acronym/name in the result of the join.

For a simple example:

WITH fruit AS
  (SELECT 1 AS id, 'apple' AS name 
  UNION ALL SELECT 2 AS id, 'pear' AS name
  UNION ALL SELECT 3 AS id, 'banana' AS name)
,
vegetable AS 
  (SELECT 1 AS id, 'courgette' AS name
   UNION ALL SELECT 2 AS id, 'cucumber' AS name
   UNION ALL SELECT 3 AS id, 'parsnip' AS name)

SELECT *
FROM fruit
INNER JOIN vegetable
ON fruit.id = vegetable.id

In Big Query this gives:

Row	id	name	id_1	name_1
1	1	apple	1	courgette
2	2	pear	2	cucumber
3	3	banana	3	parsnip

but I would like to get

Row	fruitId	fruitName	vegetableId	vegetableName
1	1	apple	1	courgette
2	2	pear	2	cucumber
3	3	banana	3	parsnip

without having to manually write aliases for each column like this:

SELECT fruit.id AS fruitId, 
  fruit.name AS fruitName, 
  vegetable.id AS vegetableId, 
  vegetable.name AS vegetableName
FROM fruit
INNER JOIN vegetable
ON fruit.id = vegetable.id

Answer 1

How about this ? though it's not 4 columns as you want. actually 2 STRUCTs.

WITH fruit AS
  (SELECT 1 AS id, 'apple' AS name 
  UNION ALL SELECT 2 AS id, 'pear' AS name
  UNION ALL SELECT 3 AS id, 'banana' AS name)
,
vegetable AS 
  (SELECT 1 AS id, 'courgette' AS name
   UNION ALL SELECT 2 AS id, 'cucumber' AS name
   UNION ALL SELECT 3 AS id, 'parsnip' AS name)
SELECT fruit, vegetable
  FROM fruit
 INNER JOIN vegetable ON fruit.id = vegetable.id
;

output:

Answer 2

You join on id so why to have it twice? Consider below

select * from (
  select *, 'fruit' as type from fruit
  union all
  select *, 'vegetable' from vegetable
)
pivot (any_value(name) name for type in ('fruit', 'vegetable'))    

if applied to sample data in your question - output is