How to unpivot multiple colums to rows in Bigquery sql-CodePudding

I'm struggling to unpivot/pivot using SQL.

I tried UNPIVOT function from BIGQUERY documentation but I succeded to unpivot only ONE colums by using the code below but not multiple at the same time

SELECT ... FROM
UNPIVOT(mail_sent FOR metric IN (mail_sent))

Here is the table I've got

month	mail_sent	mail_received	mail_opened
01	2000	1900	500
02	2200	2000	700
03	1900	1800	400

Here is what I want to have, keep the month in colums and have ALL the rest in rows :

values	01	02	03
mail_sent	2000	2200	1900
mail_received	1900	2000	1800
mail_opened	500	700	400

Any idea of how to do it ? Thanks a lot !

CodePudding user response：

Consider below approach

select * from (
  select * from your_table 
  unpivot(value for metric in (mail_sent, mail_received, mail_opened))
)
pivot (any_value(value) as _ for month in ('01', '02', '03'))

if applied to sample data in your question - output is

CodePudding user response：

I found the solution:

SELECT month, metric, value
  FROM final 
  UNPIVOT(value FOR metric IN (mail_sent, mail_received, mail_opened))
  ORDER BY 1