how to convert rows into columns in google BigQuery?

Question

Following is the input:

Expected output:

score 2 · Accepted Answer · answered Feb 25 '20 at 18:26

Assuming you have limited number of rows(months) and columns - you can use below

#standardSQL
SELECT col AS month,
  MAX(IF(month = 'Jan-20', val, NULL)) AS Jan_20,
  MAX(IF(month = 'Dec-19', val, NULL)) AS Dec_19,
  MAX(IF(month = 'Nov-19', val, NULL)) AS Nov_19,
  MAX(IF(month = 'Oct-19', val, NULL)) AS Oct_19,
  MAX(IF(month = 'Sep-19', val, NULL)) AS Sep_19
FROM `project.dataset.table`,
UNNEST(['AmountSales', 'AmountDebit', 'DebitCreditPercent']) col,
UNNEST([CASE col 
  WHEN 'AmountSales' THEN AmountSales 
  WHEN 'AmountDebit' THEN AmountDebit 
  WHEN 'DebitCreditPercent' THEN DebitCreditPercent 
  END]) val
GROUP BY col

You can test / play with above using sample/dummy data as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Jan-20' month, 1 AmountSales, 2 AmountDebit, 3 DebitCreditPercent UNION ALL
  SELECT 'Dec-19', 4, 5, 6 UNION ALL
  SELECT 'Nov-19', 7, 8, 9 UNION ALL
  SELECT 'Oct-19', 10, 11, 12 UNION ALL
  SELECT 'Sep-19', 13, 14, 15 
)
SELECT col AS month,
  MAX(IF(month = 'Jan-20', val, NULL)) AS Jan_20,
  MAX(IF(month = 'Dec-19', val, NULL)) AS Dec_19,
  MAX(IF(month = 'Nov-19', val, NULL)) AS Nov_19,
  MAX(IF(month = 'Oct-19', val, NULL)) AS Oct_19,
  MAX(IF(month = 'Sep-19', val, NULL)) AS Sep_19
FROM `project.dataset.table`,
UNNEST(['AmountSales', 'AmountDebit', 'DebitCreditPercent']) col,
UNNEST([CASE col 
  WHEN 'AmountSales' THEN AmountSales 
  WHEN 'AmountDebit' THEN AmountDebit 
  WHEN 'DebitCreditPercent' THEN DebitCreditPercent 
  END]) val
GROUP BY col

with output

Row month               Jan_20  Dec_19  Nov_19  Oct_19  Sep_19   
1   AmountSales         1       4       7       10      13   
2   AmountDebit         2       5       8       11      14   
3   DebitCreditPercent  3       6       9       12      15

Thank You Mikhail for a solution, but the number of rows(months) is not fixed that are also calculated and might be changed depends on data — LAHU BHAWAR, Feb 25 '20 at 18:28
In order to obtain the result for any number of rows you can do it in two steps as explained in this other answer from Mikhail: (https://stackoverflow.com/questions/40761453/transpose-rows-into-columns-in-bigquery-pivot-implementation). Take into account that the first query will require being run in Legacy SQL. I hope it helps :) — Joaquim, Feb 26 '20 at 16:32
Since I want a fixed number of dynamic columns names generated at run time month-wise, I have added logic to check month value to dynamically generated column names as follow: — LAHU BHAWAR, Mar 02 '20 at 17:09

score 1 · Answer 2 · answered Mar 02 '20 at 17:17

Since I want a fixed number of dynamic columns names generated at run time month-wise, I have added logic to check month value to dynamically generated column names as follow:

standardSQL

WITH project.dataset.table AS ( SELECT 'Jan-20' month, 1 AmountSales, 2 AmountDebit, 3 DebitCreditPercent UNION ALL SELECT 'Dec-19', 4, 5, 6 UNION ALL SELECT 'Nov-19', 7, 8, 9 UNION ALL SELECT 'Oct-19', 10, 11, 12 UNION ALL SELECT 'Sep-19', 13, 14, 15 ) SELECT col AS month, MAX(IF(month = Concat(FORMAT_DATE("%b", DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL -2 MONTH)),'-', substr(cast(EXTRACT(YEAR FROM DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL -2 MONTH)) as string),3,2)), val, NULL)) AS Jan_19

FROM project.dataset.table, UNNEST(['AmountSales', 'AmountDebit', 'DebitCreditPercent']) col, UNNEST([CASE col WHEN 'AmountSales' THEN AmountSales WHEN 'AmountDebit' THEN AmountDebit WHEN 'DebitCreditPercent' THEN DebitCreditPercent END]) val GROUP BY col

how to convert rows into columns in google BigQuery?

2 Answers2

standardSQL