How Can I Unpivot Data While Keeping Original Column Names in SQL?

Keep original column names for data deconvolution

Data deconvolution (Unpivoting) is a process of converting tabular data into another format, where each column becomes a row and its column names become attributes of the row. While this is typically used to reshape data for analysis or visualization, in some cases it can be useful to retain the original column names.

Question:

Given a data table with column names such as Name, Maths, Science and English, how do we deconvolve the data to obtain the following result set?

Name	Subject	Marks
Tilak	Maths	90
Tilak	Science	40
Tilak	English	60

Solution:

Using SQL’s UNPIVOT clause, we can deconvolve the data while preserving column names:

select u.name, u.subject, u.marks
from student s
unpivot
(
  marks
  for subject in (Maths, Science, English)
) u;

The

UNPIVOT clause converts the Maths, Science, and English columns into rows, and the scores become the "marks" column in the deconvolved result set. At the same time, the column name remains in the "subject" column.

Example:

Consider the following dataset:

Name	Maths	Science	English
Tilak	90	40	60
Raj	30	20	10

Applying the provided query to this dataset will produce the desired results:

Name	Subject	Marks
Tilak	Maths	90
Tilak	Science	40
Tilak	English	60
Raj	Maths	30
Raj	Science	20
Raj	English	10

This technique is useful when retaining the original column names is important for further analysis or data processing.

The above is the detailed content of How Can I Unpivot Data While Keeping Original Column Names in SQL?. For more information, please follow other related articles on the PHP Chinese website!