SQL Server Unpivot query from columns to rows

How to use the SQL Server Unpivot query to simply transform your columns in lines? The T-SQL Unpivot built-in function allows you to do it with 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 months by adding the missing ones. If you understand the syntax then it will be very easy to do.

 

The [dbo].[SALES_BY_MONTH] table contains one type column and the month’s columns containing each the number of sales. Just copy and paste the SQL query for the table creation and data insertion, and then 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.

 

We already have an example of the SQL Server Pivot function here.

 

Create the sample table with your columns to transform to rows using UNPIVOT query

Before trying the UNPIVOT query, create the sample table using this script, simply copy and paste the example in you Management Studio window.

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 first sales amount for each month
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 lines inserted into the table
SELECT * FROM dbo.SALES_BY_MONTH;

 

SQL Server Unpivot query using the built-in function to transform Columns to Rows

Build the SQL Server Unpivot in 3 steps,  it returns the column’s data in rows :

  1. Selection of the 3 result columns: Type, Month and Amount
  2. The sub-query with the original selection of data.
  3. The UNPIVOT() 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;

 

 

Still struggling with this 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.

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

 

Be the first to comment

Leave a Reply