SQL Server unpivot

Blue and grey gearwheels image

How to use a SQL Server unpivot query to transform columns to rows? It’s possible with this simple SQL Server UNPIVOT function example. The T-SQL Unpivot function can do it in one single query. In this example, only the six first months of the year are used as columns, namely January to June. But you can extend the query to all 12 periods of the year by adding the missing ones. 

Use an SQL Server UNPIVOT function example to transform easily columns to rows?

For example, the SQL Server Sales table contains one type column and the month’s columns. They contain respectively the type and the amount of sales for the month. To start, just copy and paste the SQL query for the table creation and data insertion. Add the missing months if necessary. This way you will be able to transform the 12 months of the year from columns to rows in one single SQL Server query.

 

First, create the sample table with your columns to transform to rows using UNPIVOT query.

First of all, create the sample table using this script, simply copy and paste the example in your SQL Server Management Studio window.

-- First of all we DROP the Sales table if it already exists
-- ** PLEASE REMOVE THE COMMENT IN THE DROP TABLE LINE IF NEEDED ** 
IF exists( 	SELECT 1 FROM sys.objects
            WHERE object_id = object_id(N'[dbo].[SALES_BY_MONTH]')
AND type in (N'U') )
-- BEGIN DROP TABLE [dbo].[SALES_BY_MONTH]
END;

-- SALES table creation
CREATE table [dbo].[SALES_BY_MONTH] (
    [TYPE] NVARCHAR(20),
    [January] NUMERIC(5),
    [February] NUMERIC(5),
    [March] NUMERIC(5),
    [April] NUMERIC(5),
    [May] NUMERIC(5),
    [June] NUMERIC(5)
);

-- Insert some sample sales data for each month: i.e. from January to June
INSERT INTO dbo.SALES_BY_MONTH ( [TYPE], [January], [February], [March], [April], [May], [June] )
VALUES ( N'Sales', 1000, 2000, 3000, 4000, 5000, 6000);

INSERT INTO dbo.SALES_BY_MONTH ( [TYPE], [January], [February], [March], [April], [May], [June] )
VALUES ( N'Discounts', 100, 200, 300, 400, 500, 600);

INSERT INTO dbo.SALES_BY_MONTH ( [TYPE], [January], [February], [March], [April], [May], [June] )
VALUES ( N'Offers', 10, 20, 30, 40, 50, 60);

-- Check the lines inserted in the table
SELECT * FROM dbo.SALES_BY_MONTH;
Sales by Month to be transformed SQL Server UNPIVOT function image.
Sample Sales data to be used by SQL Server UNPIVOT function.

 

 

Then write this SQL Server UNPIVOT query to transform the columns to rows.

Finally, the SQL Server UNPIVOT function is coompound of 3 different steps  to transpose the columns in rows.

  1. The selection of the 3 result columns, i.e. Type, Month and Amount.
  2. Then the SQL sub-query with the original selection of data.
  3. The UNPIVOT operation itself with the 6 months named explicitly.
-- UNPIVOT Columns to Rows with SQL Query
SELECT [TYPE], Month, Amount
FROM (	
	SELECT [TYPE], [January], [February], [March], [April], [May], [June]
	FROM dbo.SALES_BY_MONTH 	) sbm
UNPIVOT
   (Amount FOR Month IN 
      ([January], [February], [March], [April], [May], [June])
)AS SalesUnPivot;
Screenshot of data transformed by the SQL Server UNPIVOT function.
The query using the SQL Server UNPIVOT function and the result displayed.

In addition, if you are still struggling with the UNPIVOT function syntax ? Just copy paste the example and repeat the three steps by adapting to your real case. Start by renaming the columns to understand how it works.

In conclusion, the official Microsoft documentation for the function is available here : https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

The UNPIVOT is the opposite of the PIVOT function, here is an example of the SQL Server PIVOT function.

Be the first to comment

Leave a Reply

Your email address will not be published.


*