How to split one single column to multiple columns in Standard sql- Big query

Question

Using Google BigQuery

  WITH prep AS (
  SELECT 
  SPLIT(offer.name, '-') AS split,
  id
  FROM `hasoffer.offers_new` AS offer
  )

 SELECT *
 FROM
 (
  SELECT
  split[SAFE_ORDINAL(1)] AS Game,
  split[SAFE_ORDINAL(2)] AS GEO,
  IF ( REGEXP_CONTAINS ('INTERNAL - POGED',offers.name), "POGED", 
  IF(REGEXP_CONTAINS ('INTERNAL',offers.name), "Internal", IF( 
  REGEXP_CONTAINS('REDIRECT',offers.name), "REDIRECT", "Public"))) AS 
  Category,
  DATE(conv.datetime) AS Date,
  (SUM(conv.revenue_cents)/100) AS Revenue
  FROM `hasoffer.offers_new` AS offers
  LEFT JOIN
  prep
  ON
  offers.id=prep.id
  LEFT JOIN
  `hasoffer.conversions` AS conv
  ON offers.id = conv.offer_id
  WHERE 
  conv.advertiser_id = 632 
  AND EXTRACT(MONTH FROM conv.datetime) = EXTRACT(MONTH FROM CURRENT_DATE())
  AND EXTRACT(YEAR FROM conv.datetime) = 2018
  AND conv.status != 'rejected'
  AND conv.affiliate_sub1 IS NOT NULL
  group by
  GEO,
  Game,
  Category,
  Date
  order by
  Date)AS SourceTable

This is how the table looks:

Expected output:

• The column "Date" has to be split into multiple columns each representing a day in a month and should contain value of sum of revenue for different Games and Geos.

• Additionally, a column called Grand total must be created which gives the total revenue of game ad geo for the month. Please suggest a way to do that

Answer 1

To do the pivotting you want using BigQuery you could use...

SELECT
  Game,
  Geo,
  Category,
  SUM(CASE WHEN EXTRACT(DAY FROM Date) = 01 THEN Revenue ELSE 0 END)   AS DAY_OF_MONTH_01,
  SUM(CASE WHEN EXTRACT(DAY FROM Date) = 02 THEN Revenue ELSE 0 END)   AS DAY_OF_MONTH_02,
  SUM(CASE WHEN EXTRACT(DAY FROM Date) = 03 THEN Revenue ELSE 0 END)   AS DAY_OF_MONTH_03,
  ...
  SUM(CASE WHEN EXTRACT(DAY FROM Date) = 30 THEN Revenue ELSE 0 END)   AS DAY_OF_MONTH_30,
  SUM(CASE WHEN EXTRACT(DAY FROM Date) = 31 THEN Revenue ELSE 0 END)   AS DAY_OF_MONTH_31,
  SUM(Revenue)                                                         AS GRAND_TOTAL
FROM
  yourSource
WHERE
      Date >= DATE '2018-08-01'
  AND Date <  DATE '2018-09-01'
GROUP BY
  Game,
  Geo,
  Category

The column headings then don't contain the months, but that's because they can't be derived from the data. I still think you should pivot in your spreadsheet, using this query as the source...

SELECT
  Game,
  Geo,
  Category,
  Date,
  SUM(Revenue)   AS Revenue
FROM
  yourSource
WHERE
      Date >= DATE '2018-08-01'
  AND Date <  DATE '2018-09-01'
GROUP BY
  Game,
  Geo,
  Category,
  Date

Then you can't have "huge" data, you just have what you need, and let the sheet pivot it...