How Can I Unpivot Data and Retain Column Names in SQL?

Use the "reverse" operation to reconstruct data and preserve column names

The "reverse" operation is an important tool for reconstructing tabular data. It can convert the data from column-oriented format to row-oriented format. A common scenario is to extract data and column names from the original table.

Consider the following table called StudentMarks, which tracks student performance in different subjects:

姓名	数学	科学	英语
Tilak	90	40	60
Raj	30	20	10

The desired output is a reconstructed table with three columns: name, subject and score:

姓名	科目	分数
Tilak	数学	90
Tilak	科学	40
Tilak	英语	60

To achieve this we can use the "invert" operation as shown below:

<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 successfully reverses the data in the StudentMarks table while preserving name and account information. The "FOR" clause specifies the column names from which data will be extracted.

As you mentioned, your original query successfully reversed the name and fraction values, but was missing the account column. The modified query solves this problem by including the account column in the final select list.

This solution provides a flexible way to reconstruct tabular data, enabling the extraction of data values ​​and corresponding column names.

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