PDE Preparing and Using Data for Analysis Practice Question
You have a BigQuery table with sales data and want to pivot product categories into columns. Which SQL clause should you use?
⚠ Common exam trap
PDE often tests the confusion between PIVOT and UNPIVOT — candidates must remember PIVOT turns rows into columns, while UNPIVOT turns columns into rows.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
PIVOT
BigQuery supports the PIVOT operator, which rotates rows into columns by aggregating values for each distinct pivot key. It is used in the FROM clause after the source table or subquery, with an aggregate function and a list of pivot values. This is the standard SQL way to turn product categories into columns in a sales table.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
UNPIVOT
Why it's wrong here
UNPIVOT rotates columns into rows, the opposite transformation to the required pivot. The scenario needs category values turned into columns, which the PIVOT operator performs. UNPIVOT would be correct when wide columns must be flattened into key-value rows for analysis.
- ✓
PIVOT
Why this is correct
PIVOT rotates rows into columns, directly satisfying the requirement to turn product categories into separate columns. BigQuery supports the PIVOT operator, which aggregates values using an aggregate function alongside a FOR clause naming the category column and an IN list of the values to become column headers.
- ✗
ARRAY_AGG with CROSS JOIN
Why it's wrong here
ARRAY_AGG gathers values into a repeated array within a single row; it cannot spread category values across separate output columns. Pivoting requires the PIVOT operator, which aggregates and rotates rows into named columns. ARRAY_AGG suits denormalising related values into nested structures, not column rotation.
- ✗
STRUCT
Why it's wrong here
STRUCT creates nested record fields within a row, not columns derived from row values. Pivoting category values into columns requires the PIVOT operator with an aggregate. It is tempting because STRUCT organises related data, but it cannot transpose rows into columns.
About these practice questions
This PDE question is part of Courseiva's 747-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Google Cloud exam blueprint
This PDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PDE exam.