How Can SQL Unpivoting Transform Wide Tabular Data into a Long Format?

Unpivoting: Restructuring Relational Data

Imagine a table, StudentMarks, storing student names (Name) and their scores in various subjects (Maths, Science, English). The data is initially organized this way:

<code>Name, Maths, Science, English
Tilak, 90, 40, 60
Raj, 30, 20, 10</code>

But, what if we need a different structure—one where each subject's score appears on a new row? The desired outcome looks like this:

<code>Name, Subject, Marks
Tilak, Maths, 90
Tilak, Science, 40
Tilak, English, 60
Raj, Maths, 30
Raj, Science, 20
Raj, English, 10</code>

This is where SQL unpivoting comes in. It's a powerful technique for transforming wide tables (many columns) into long tables (many rows).

Here's how to accomplish this using a SQL query:

<code class="language-sql">SELECT u.name, u.subject, u.marks
FROM student s
UNPIVOT
(
  marks
  FOR subject IN (Maths, Science, English)
) u;</code>

This query uses the UNPIVOT operator. It takes the Maths, Science, and English columns and transforms them into a single Subject column, with the corresponding scores in the Marks column. The result is the desired long format.

For a live demonstration, check out this SQL Fiddle:

SQL Fiddle Example

The above is the detailed content of How Can SQL Unpivoting Transform Wide Tabular Data into a Long Format?. For more information, please follow other related articles on the PHP Chinese website!