CODEWITHSUNDEEP
sqlgoogle-bigquery
Andrew Seaman
Andrew Seaman

Reputation: 47

Transpose Dynamic Rows to Columns BigQuery

I have a table that adds new rows each Friday with a Report_Date attached. I will provide a sample table below:

Report_Date   Value   Item
1/1/2019       5      green
1/1/2019       5      blue
1/1/2019       4      red
1/8/2019       5      green
1/8/2019       6      blue
1/8/2019       3      red
1/15/2019      6      green
1/15/2019      5      black
1/15/2019      4      blue
1/15/2019      5      white

My desired result is to have a dynamic query that produces the below, and will add a new column each week as the table expands.

1/1/2019   1/8/2019   1/15/2019
  5           5          6
  5           6          5
  4           3          4
 null       null         5

So far, I've been trying to use a full outer join to get what I need but this script is inefficient and will need to be rewritten every week. Note I am using Bigquery for this.

SELECT a.value AS WK1, b.value AS WK2
FROM table_1 a
FULL OUTER JOIN table_1 b
ON a.Item = b.Item
WHERE a.Report_Date = '1/1/2019'
AND b.Report_Date = '1/8/2019'

Upvotes: 0

Views: 664

Answers (1)

Y.K.
Y.K.

Reputation: 692

if data is always collected on the same day, you can do something like this

with

sample_data as (
    select
        *
    from
        unnest(
            array[
                struct(date'2019-01-01' as report_date, 5 as value, 'green' as item),
                struct(date'2019-01-01' as report_date, 5 as value, 'blue' as item),
                struct(date'2019-01-01' as report_date, 4 as value, 'red' as item),
                struct(date'2019-01-08' as report_date, 5 as value, 'green' as item),
                struct(date'2019-01-08' as report_date, 6 as value, 'blue' as item),
                struct(date'2019-01-08' as report_date, 3 as value, 'red' as item),
                struct(date'2019-01-15' as report_date, 6 as value, 'green' as item),
                struct(date'2019-01-15' as report_date, 5 as value, 'black' as item),
                struct(date'2019-01-15' as report_date, 4 as value, 'blue' as item),
                struct(date'2019-01-15' as report_date, 5 as value, 'white' as item)
            ]
        )
)

select
    sd.item,

    md.max_report_date,
    sum(if( sd.report_date = md.max_report_date,
            sd.value, null)) as last_week_value,

--                  uncomment if need this periods

--     date_sub(md.max_report_date, interval 7 day) as prev_report_date,
    sum(if( sd.report_date = date_sub(md.max_report_date, interval 7 day),
            sd.value, null)) as prev_week_value,

--     date_sub(md.max_report_date, interval 7 * 2 day) as last_minus_2_report_date,
    sum(if( sd.report_date = date_sub(md.max_report_date, interval 7 * 2 day),
            sd.value, null)) as last_minus_2_week_value,

--     date_sub(md.max_report_date, interval 7 * 3 day) as last_minus_3_report_date,
    sum(if( sd.report_date = date_sub(md.max_report_date, interval 7 * 3 day),
            sd.value, null)) as last_minus_3_week_value
from
    sample_data as sd
    cross join (select
                    max(report_date) as max_report_date
                from
                    sample_data) as md
group by
    1, 2

Upvotes: 2

Related Questions