Master Pivot Unpivot SQL Server Example with Easy Tutorials

In the realm of data manipulation, particularly in Microsoft SQL Server, the concepts of 'pivot' and 'unpivot' are invaluable tools. These set-based operations enable you to convert rows into columns and vice versa, transforming your data into a more report-friendly or analysis-ready format.

Impress Your Coworkers by Using SQL UNPIVOT!

In this article, we will delve into the world of 'pivot' and 'unpivot' in SQL Server, providing practical examples and detailed explanations to help you master these powerful transformations.

Understanding Pivot in SQL Server

The PIVOT operator rotates a table value by turning rows into columns. It's like swapping the rows and columns of a matrix. This can be particularly useful when you want to analyze data in a more tabular format.

SQL Pivot in all databases: MySQL, MariaDB, SQLite, PostgreSQL, Oracle, …

Here's a simple example. Suppose we have a table 'Sales' with columns 'Customer', 'Category', and 'SalesAmount'. Instead of having separate rows for each customer and category, we want to see a summary table with categories as columns. We can achieve this using PIVOT:

Basic Pivot Example

How to Pivot Your Online Business: A 3-Step Audit & Strategy Guide

First, let's see our original 'Sales' table:

```sql SELECT Customer, Category, SalesAmount FROM Sales ```

Now, let's pivot this table to turn 'Category' into columns:

```sql SELECT * FROM ( SELECT Customer, Category, SalesAmount FROM Sales ) AS src PIVOT ( SUM(SalesAmount) FOR Category IN ([Electronics], [Appliances], [Clothing]) ) AS pvt ```

Dynamic Pivot Example

Excel Pivot Table Explained (Part 1) | Easy Guide for Beginners with Example

In the previous example, we hardcoded the column names. However, you can use the dynamic method to generate column names from a list:

```sql DECLARE @columns NVARCHAR(MAX)='', @Sql NVARCHAR(MAX); SELECT @columns += QUOTENAME(c.Category) + ',' FROM (SELECT DISTINCT Category FROM Sales) AS c; SET @columns = LEFT(@columns, LEN(@columns) - 1); SET @Sql = 'SELECT * FROM ( SELECT Customer, Category, SalesAmount FROM Sales ) AS src PIVOT ( SUM(SalesAmount) FOR Category IN (' + @columns + ') ) AS pvt'; EXEC sp_executesql @Sql; ```

Understanding Unpivot in SQL Server

Unpivot does the opposite of pivot - it converts columns into rows. If you've got a table with extensive columns and want to flatten it into rows for easier analysis, unpivot comes to the rescue.

10k views on how to use pivot table in excel

Let's consider we have a 'SalesSummary' table with separate columns for each month's sales. To get a consolidated view of all months in one table, we can use UNPIVOT:

Basic Unpivot Example

the pivot tables training poster is shown in blue and green, with instructions on how to

Pivot Table Secrets : Repeat Label Items and Tabular Form in Excel #excel #exceltips #exceltutorial

an image of the server list for windows and mac oss servers, with different types of

Sql Cheat Sheet, Learn Sql, Learn Web Development, Excel Tutorials, Sql Server, Career Options, Data Analyst, Data Scientist, Data Analysis

How to Setup Source Data for Pivot Tables - Unpivot in Excel

SQL RIGHT() Explained with Examples | Extract Characters from the Right in SQL Server

SQL UPPER() Explained with Examples | Convert Text to Uppercase in SQL

an info poster showing the different types of web pages and how they are used to create them

Consolidate multiple excel sheets into one Pivot Table - EXTENDED version

SQL REPLACE() Explained with Examples | Replace Text in SQL Server

SQL LEFT() Explained with Examples | Extract Characters from the Left in SQL Server

7 SQL Projects for Beginners | Build a Real Data Portfolio

Pivot Tables Made Easy 📊 | Excel & Google Sheets Beginner Guide

SQL SUBSTRING() Explained with Examples | Extract Text in SQL Server

First, our 'SalesSummary' table:

```sql SELECT Customer, Jan, Feb, Mar FROM SalesSummary ```

Now, let's unpivot this table to turn month columns into rows:

```sql SELECT Customer, Month, SalesAmount FROM ( SELECT Customer, Jan AS JanSales, Feb AS FebSales, Mar AS MarSales FROM SalesSummary ) AS src UNPIVOT ( SalesAmount FOR Month IN (JanSales, FebSales, MarSales) ) AS unpvt ```

Dynamic Unpivot Example

Similar to dynamic pivot, you can use dynamic SQL for the UNPIVOT operator as well:

```sql DECLARE @columns NVARCHAR(MAX)='', @Sql NVARCHAR(MAX); SELECT @columns += QUOTENAME(c.Month) + ',' FROM (SELECT DISTINCT Month FROM SalesSummary) AS c; SET @columns = LEFT(@columns, LEN(@columns) - 1); SET @Sql = 'SELECT Customer, Month, SalesAmount FROM ( SELECT Customer, ' + @columns + ' FROM SalesSummary ) AS src UNPIVOT ( SalesAmount FOR Month IN (' + @columns + ') ) AS unpvt'; EXEC sp_executesql @Sql; ```

Mastering 'pivot' and 'unpivot' opens up a world of possibilities for data transformation and analysis in SQL Server. Practice these examples, experiment with your data, and soon you'll be pivoting and unpivoting like a pro!