Databricks Data Engineer Associate Question 191
Single answerA retail company stores its sales data in a table named 'sales', which includes columns 'store_id', 'month', and 'revenue'. The company wants to convert this data from a long format to a wide format, where each month becomes a separate column and the values represent revenues for each store. Which SQL operation should you use to achieve this transformation in Databricks?
- A
PIVOT
- B
UNPIVOT
- C
GROUP BY with aggregation
- D
JOIN
Show answer and explanation
Correct answer: A
Explanation
The PIVOT clause is specifically designed to transform data from a long format to a wide format by converting unique values from one column into multiple columns. In the given scenario, each unique month should become a column, with revenue values filling the corresponding cells for each store_id. This functionality is achieved using the PIVOT operation in Databricks.
- A. Correct.
Correct. The PIVOT clause is used to convert data from a long format to a wide format by transforming rows into columns. It is the appropriate choice for this scenario.
- B. Incorrect.
Incorrect. The UNPIVOT clause is used to convert data from a wide format to a long format, performing the opposite operation of PIVOT.
- C. Incorrect.
Incorrect. GROUP BY with aggregation is used to summarize data across groups but does not transform rows into columns.
- D. Incorrect.
Incorrect. JOIN combines data from multiple tables based on a common key but does not perform row-to-column transformations.