How to Unpivot Data and Preserve Column Names in SQL?

SQL destructures data and preserves column names

This article will guide you on how to deconstruct tabular data while preserving column names.

Example

Suppose there is a table named StudentMarks, containing Name, Maths, Science and English columns. The data format is as follows:

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

Goal

The goal is to convert the data into the following format:

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

SQL statement

In order to achieve the expected results, you need to use the UNPIVOT statement for data conversion and extract the account name:

<code class="language-sql">SELECT Name, Subject, Marks
FROM StudentMarks
UNPIVOT
(
    Marks
    FOR Subject IN (Maths, Science, English)
) AS UnPvt;</code>

Description

• The UNPIVOT clause converts data from rows to columns.
• The FOR clause specifies the columns to be destructured.
• AS UnPvt alias assigns a name to the destructured table.
• The SELECT clause selects columns from the destructured table.

With the above steps, you can successfully deconstruct tabular data and preserve column names in the final result.

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