Search code examples
google-bigquerypivot-tablelooker

Big query Pivoting with a specific requirement

I have used pivot in big query, but here is a specific use case and the data that I need to show in looker. I am trying the similar option in looker but wanted to know if I can just show this in big query.

This is how my data (Sample) in BIG QUERY table is:

Sample data for table "BIG_QUERY"

The output should be as below:

Sample Output

If you look at it, it's pivoting but I need to assign the column names as shown (for the specific range) and for the range 6 and more, I need to add the pivot columns data into one.

I don't see pivot index or something like this in BIG_QUERY. Was thinking if there is a way to sum up the column data after pivot index 6 or so? Any suggestions how to achieve this?

Solution

  • Hope below approach would be helpful,

    SELECT * FROM (
  SELECT Node, bucket, total_code 
    FROM sample, UNNEST([RANGE_BUCKET(data1, [1, 2, 3, 4, 5, 6, 7])]) bucket
) PIVOT (SUM(total_code) `range` FOR bucket IN (1, 2, 3, 4, 5, 6, 7));

    output:

    enter image description here