How to Unpivot Data and Include Column Names as a New Column in SQL?

Transforming Wide Data into Long Format in SQL

Suppose you have a table called StudentMarks with columns Name, Maths, Science, and English. The goal is to convert this wide-format data into a long format, where subject names become a new column. The target output should resemble this:

<code>Name  Subject  Marks
Tilak Maths    90
Tilak Science  40
Tilak English  60</code>

A common approach using the UNPIVOT operator might initially seem challenging for including column names as a new column. However, the solution is straightforward:

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

This SQL query uses UNPIVOT to restructure the StudentMarks table (aliased as 's'). The FOR clause within UNPIVOT specifies the columns to be unpivoted (Maths, Science, English), and these column names automatically populate the new Subject column in the resulting dataset. The marks values are assigned to the marks column in the unpivoted table. This revised query efficiently generates the desired Name, Subject, and Marks columns in the long format.

The above is the detailed content of How to Unpivot Data and Include Column Names as a New Column in SQL?. For more information, please follow other related articles on the PHP Chinese website!