Databricks Certified Data Engineer - Associate

Explanation

The correct answer is B. PIVOT.

Why PIVOT is correct:

In SQL, the PIVOT operation is specifically designed to convert data from a long format (also known as unpivoted or normalized format) to a wide format (also known as pivoted or denormalized format).

Long format typically has:

• Multiple rows for the same entity
• One column for the variable names
• One column for the variable values

Wide format typically has:

• One row per entity
• Multiple columns for different variables

Why other options are incorrect:

A. TRANSFORM - This is not a standard SQL keyword for reshaping data. In some contexts, it might refer to data transformation, but not specifically for converting between long and wide formats.

C. SUM - This is an aggregate function used for mathematical summation, not for reshaping data formats.

D. CONVERT - This function is used for data type conversion (e.g., converting a string to a date), not for reshaping table structures.

E. WHERE - This is a clause used for filtering rows based on conditions, not for changing the format of the data.

Additional context:

In Spark SQL and many other SQL dialects, PIVOT is the standard operation for this transformation. The PIVOT operation takes values from a column and turns them into multiple columns in the output, effectively converting long format data to wide format data.

Example syntax in Spark SQL:

SELECT *
FROM table
PIVOT (
  SUM(value)
  FOR category IN ('A', 'B', 'C')
)

SELECT *
FROM table
PIVOT (
  SUM(value)
  FOR category IN ('A', 'B', 'C')
)

This would convert rows with different categories into columns with aggregated values.