title: How to split one single column to multiple columns in Standard sql- Big query
body: <p>Using Google BigQuery</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>This is how the table looks:</p>
  
  <p><img src="https://i.stack.imgur.com/lH8Ru.jpg" alt="This is how the table looks"></p>
  
  <p>Expected output:</p>
  
  <p><img src="https://i.stack.imgur.com/KhL1j.jpg" alt="Expected Output"></p>
  
  <ul>
  <li><p>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.</p></li>
  <li><p>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</p></li>
  </ul>