Databricks Certified Data Engineer - Associate

Explanation

The correct answer is PIVOT.

Why PIVOT is correct:

1. PIVOT operation: In SQL, the PIVOT operation is specifically designed to transform data from a long format (multiple rows for each category) to a wide format (multiple columns for each category).
2. Data transformation: PIVOT rotates data from rows to columns, aggregating values where necessary.
3. Common use case: This is commonly used to create cross-tabulation reports where you want to see values across different categories as separate columns.

Why other options are incorrect:

• TRANSFORM: This is not a standard SQL keyword for converting table formats. It might be used in some specific contexts but not for pivoting data.
• SUM: This is an aggregation function that calculates the sum of values, not for converting table formats.
• CONVERT: This is typically used for data type conversion (e.g., converting strings to dates), not for changing table structure from long to wide format.
• WHERE: This is a clause for filtering rows based on conditions, not for transforming table structure.

Example of PIVOT in SQL:

SELECT *
FROM sales_data
PIVOT (
  SUM(amount)
  FOR product_category IN ('Electronics', 'Clothing', 'Books')
) AS pivoted_table;

SELECT *
FROM sales_data
PIVOT (
  SUM(amount)
  FOR product_category IN ('Electronics', 'Clothing', 'Books')
) AS pivoted_table;

This would convert long-format sales data with multiple rows per product category into wide format with separate columns for each product category.