CODEWITHSUNDEEP
google-bigqueryunpivot
Nobbies_data
Nobbies_data

Reputation: 31

Unpivot to create multple columns

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

Upvotes: 0

Views: 438

Answers (2)

Jaytiger
Jaytiger

Reputation: 12234

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:

enter image description here

Upvotes: 2

dikesh
dikesh

Reputation: 3125

Here is how you can use UNPIVOT

SELECT * FROM table_1
UNPIVOT(
  (players, positions) 
  FOR player_position 
  IN  ((player1, position1), (player2, position2), (player3, position3))
)

Output

match players positions player_position
54575 234 535 player1_position1
54575 568 78758 player2_position2
54575 98000 8686586 player3_position3

Upvotes: 1

Related Questions