Unpivot using SQL

When converting columns into rows in SQL, unpivoting is used, effectively converting wide format (i.e., data with many columns) into long format (i.e., data with fewer columns and more rows). The process of pivoting, which includes turning rows into columns, is the opposite of this. 

Here is the syntax of Unpivot in SQL:

SELECT [column1], [column2], [column3]

FROM (

  SELECT [id], [column1], [column2], [column3]

  FROM [table_name]

) AS [source_table]

UNPIVOT (

  [value] FOR [column_name] IN ([column1], [column2], [column3])

) AS [unpivot_table];

In this syntax:

• [column1], [column2], and [column3] are the columns you want to unpivot.
• [table_name] is the name of the table containing the data you want to unpivot.
• [id] is a column that uniquely identifies each row in the table.
• [value] is the name of the column that will contain the values that were previously in the unpivoted columns.
• [column_name] is the name of the column that will contain the original column names.
• [unpivot_table] is the name of the table that will contain the unpivoted data.

Here is an example of unpivot operation in SQL using the UNPIVOT keyword:

SELECT * FROM ( SELECT id, col1, col2, col3 FROM test_table ) AS t UNPIVOT ( value FOR column_name IN (col1, col2, col3) ) AS u;

Pick the test_table table's test_table columns (col1, col2, and col3) that will be unpivoted first in this example. Use the UNPIVOT keyword to unpivot the columns you defined earlier (col1, col2, and col3) as well as the new column names (value and column_name). The output will contain a row for each possible pairing of id and column_name, with the value of the unpivoted column showing in the value column.

Consider a table called sales with the columns id, year, sales_Q1, sales_Q2, sales_Q3, sales_Q4.

To unpivot the sales_Q1, sales_Q2, sales_Q3, sales_Q4 columns into a single column called sales, use the following SQL query:

SELECT id, year, sales_quarter, sales FROM ( SELECT id, year, sales_Q1, sales_Q2, sales_Q3, sales_Q4 FROM sales ) AS source_table UNPIVOT ( sales FOR sales_quarter IN (sales_Q1, sales_Q2, sales_Q3, sales_Q4)

) AS unpivot_table;

The result set will look like as follows:

In the above example, the sales_Q1, sales_Q2, sales_Q3 and sales_Q4 columns have been unpivoted into a single sales column, with a new sales_quarter column indicating which quarter the sales value belongs to.