Unpivot to create multple columns

Question

I have this table .....

match  |player1  |player2  |player3 |position1  |position2  |position3 |
------------------------------------------------------------------------
54575  |234      |568      |98000   |535        |78758      |8686586   |

and i want to make unpivot (with BIGQUERY) to have his output :

match  |player  |position 
--------------------------
54575  |234      |535     
54575  |568      |78758   
54575  |98000    |8686586  

I try this query ( https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator )

SELECT
  *
FROM
  match UNPIVOT(id FOR player IN (player1,
      player2,
      player3))

AND i have this result :

product |position1 |position2 |position3|id   |player   
----------------------------------------------------
54575   |  535     |78758     |8686586  |234  |player1
54575   |  535     |78758     |8686586  |568  |player2
54575   |  535     | 78758    |8686586  |98000|player3

Answer 1

Another option without UNPIVOT

SELECT match, pp.* 
  FROM sample, UNNEST([
    STRUCT(player1 AS player, position1 AS position),
          (player2, position2),
          (player3, position3)
  ]) pp;

output will be: