Just like in other data preparation methods there is a way to pivot and unpivot in SQL. Let's start with Unpivot.
The unpivot function rotates a table in a way where columns becomes rows. Let's say this is the table we want to pivot:
Pretty simple, we currently have a row for a player and a column for each statistics. Instead, we are looking to have a row for each statistic for each player.
When we look at the documentation for Unpivot there are 3 arguments to pass.
Unpivot ( Argument 1 FOR Argument 2 IN (Argument 3)
The first two arguments represent the name of the two columns you want to create. In the chart below, the values which will populate your first argument are highlighted in green, while the values which will population your second argument are in red. Make sure to give your two new columns a good name!
Your third argument, and this is where it can feel confusing, is the columns you want to unpivot. In our case we would want, goals, assist and cards to become a row each. Our argument 3 will therefore be: Goals, Assist, Cards (If necessary add the name of the table before such as game.Goals).
This will give us our table with 3 columns (players, statistic and value) and 9 rows (10 including headers) .
The Pivot function is similar. However, it changes the table so rows become columns. The exact opposite of unpivot. Here is the function once again:
PIVOT ( Argument 1 FOR Argument 2 IN Argument 3)
Argument 1 has to be an aggregation, for example Sum() or Min(). This is because your first argument represents the values which will populate your new columns created. Your second argument is the field from which you get the new column's names out of, this means you have to pick an existing column. Finally, your third argument is the columns you want to create. This means that your third argument has to be values from the column you chose to be your second argument.