CODEWITHSUNDEEP
sqlgoogle-bigquery
outboundbird
outboundbird

Reputation: 633

Index column in SELECT EXCEPT() after joining table using default table name as prefix

This question is following up from the question originally regarding renaming the column names with table names as prefixes in google big query. Long story short, I need to join two tables with exact names and keep the origins of the columns.

I learned that I can use, for example: 

SELECT table_A , table_B
FROM table_A
FULL JOIN table_B
USING (KEY1, KEY2, KEY3)

This code would generate a joined table with column names for example: table_A.KEY1, table_B.KEY1 in the result on google big query.

However I have trouble to deselect some of the columns from the joined table. For example, 

WITH merged AS (
    SELECT table_A , table_B
    FROM table_A
    FULL JOIN table_B
    USING (KEY1, KEY2, KEY3)
)

SELECT * EXCEPT(table_A.KEY1, table_B.KEY1)
FROM merged

I got this error

Syntax error: Expected ")" or "," but got "."

Does anybody have any good idea how to circumvent this issue? Thank you in advance!

This manipulation is out of the purpose of reshaping long table into wide table. For example, I have a long table 

KEY1 KEY2 KEY3 VALUE1 VALUE2 DOC_TYPE
1     2     3   A      Q     PAY
1     2     3   A      Q     INVOICE
2     3     4   D      B     PAY
2     3     4   D      B     INVOICE

and ideally reshape into the wide table using KEY1 KEY2 KEY3:

KEY1 KEY2 KEY3 VALUE1.pay VALUE2.pay VALUE1.inv VALUE2.inv 
1     2     3   A         Q          A          Q   
2     3     4   D         B          D          B

Since the table contains many columns. It is impractical to rename all of them manually. I wonder if Google big query can have some shortcut to do it.

Upvotes: 0

Views: 669

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL (and I am re-using naming from your original question and respective answer so to preserve context) 

#standardSQL
SELECT 
  key1, key2, key3, key4, 
  (SELECT AS STRUCT inv.* EXCEPT(key1, key2, key3, key4)) inv, 
  (SELECT AS STRUCT prof.* EXCEPT(key1, key2, key3, key4)) prof
FROM inv FULL JOIN prof
USING (key1, key2, key3, key4)

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33725

WITH merged AS (
    SELECT table_A , table_B
    FROM table_A
    FULL JOIN table_B
    USING (KEY1, KEY2, KEY3)
)

SELECT
  (SELECT AS STRUCT table_A.* EXCEPT(KEY1)) AS table_A,
  (SELECT AS STRUCT table_B.* EXCEPT(KEY1)) AS table_B
FROM merged

Upvotes: 1

Related Questions