CODEWITHSUNDEEP
google-sheetsgoogle-bigquerygoogle-sheets-formula
nickcitro
nickcitro

Reputation: 31

Transpose Boolean Columns to Header Names (Google Sheets to BigQuery SQL)

I have a spreadsheet that currently includes the following columns:

event_date is a date type, event_id an integer, and studio_1 & studio_2 are both TRUE/FALSE (checkbox in Sheets) Example schema:

event_date event_id studio_1 studio_2
2024-03-15 1234 TRUE FALSE
2024-03-16 5678 TRUE TRUE

I want to transpose these values to stack the studio_1 & studio_2 columns on top of one another, and rather than TRUE, call out the name of the studio that is booked in a new column called studio_booked. How I want it to look:

event_date event_id studio_booked
2024-03-15 1234 Studio 1
2024-03-16 5678 Studio 1
2024-03-16 5678 Studio 2

Basically I want to generate a single column noting the studio that is booked with duplication of the date and event_id columns so I can count total bookings in each studio per date/event_id while also being able to filter on the space name in a single column rather than needing to fitler on each studio's column where TRUE.

This data is intened to be loaded into a dashboard via SQL script in which the current database schema matches the above schema. Are there any combinations of functions/formulas that can be used to transform the data in Sheets before being uploaded? Would also appreciate guidance on writing a SQL statement to handle the transformation in BigQuery instead.

Upvotes: 2

Views: 107

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173161

Consider below (for BigQuery) approach

select * except(booked)
from your_table
unpivot(booked for studio_booked in (studio_1, studio_2))
where booked

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

balintd
balintd

Reputation: 197

There might be a shorter solution, but here is a GoogleSQL script that does the job. With UNION, you can concatenate rows (vertically):

SELECT event_date, event_id, 'Studio 1' AS studio_booked
FROM spreadsheet
WHERE studio_1 IS TRUE
UNION ALL
SELECT event_date, event_id, 'Studio 2' AS studio_booked
FROM spreadsheet
WHERE studio_2 IS TRUE

Upvotes: 1

Related Questions