John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

How to write a PIVOT query in T-SQL

12th January 2016 By John McCormack Leave a Comment

sql server pivotIn this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is

The central point, pin, or shaft on which a mechanism turns or oscillates.

So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.

Create the table and data which we will use in out PIVOT query

Lets create a database table which records the size of different tables within a database over time.

[sql]
CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)

[/sql]

OK, now normally we would have an automated process in place that populate this table for every table within every database on your instance. We could even enhance this to store every table within every database within every instance on a central collection server. Since this demo requires some immediate data however, you can use the query below to insert data from 3 different tables over the course of 1 year.

[sql]

— INSERT INTO SalesOrderDetail
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,2000,’01/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,3000,’02/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,4000,’03/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,5000,’04/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,6000,’05/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,7000,’06/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,8000,’07/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,9000,’08/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,10000,’09/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,11000,’10/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,12000,’11/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderDetail’,13000,’12/01/2015′)

— INSERT INTO SalesOrderHeader
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,500,’01/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,1000,’02/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,1500,’03/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,2000,’04/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,2500,’05/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,3000,’06/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,3500,’07/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,4000,’08/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,4500,’09/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,5000,’10/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,5500,’11/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’SalesOrderHeader’,6000,’12/01/2015′)

— INSERT INTO Customers
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,1000,’01/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,2000,’02/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,3000,’03/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,4000,’04/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,5000,’05/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,7000,’06/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,9000,’07/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,13000,’08/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,14000,’09/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,15000,’10/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,19000,’11/01/2015′)
INSERT INTO dbo.TableSize (DatabaseName,Name,DataKB,Collection_Date)
VALUES (‘Sales’,’Customers’,21000,’12/01/2015′)

[/sql]

To see the type of data we would normally get back from this query, we could run:

[sql]

SELECT
DatabaseName,
Name,
DataKB,
Collection_Date
FROM dbo.TableSize

[/sql]

This returns a data set that looks like this with 36 rows.

t-sql query output without using pivot

Using the PIVOT operator

Now we’ll amend the query by making the original query a CTE and then applying the PIVOT operator.

[sql]

— The PIVOT query
;WITH PIVOTTABLE as
(
Select
DatabaseName,
Name,
DataKB,
Collection_Date
FROM dbo.TableSize
)
SELECT
DatabaseName,
Name,
[2015-01-01],
[2015-02-01],
[2015-03-01],
[2015-04-01],
[2015-05-01],
[2015-06-01],
[2015-07-01],
[2015-08-01],
[2015-09-01],
[2015-10-01],
[2015-11-01],
[2015-12-01]
FROM PIVOTTABLE
pivot (SUM (DataKB) for Collection_Date in
(
[2015-01-01],
[2015-02-01],
[2015-03-01],
[2015-04-01],
[2015-05-01],
[2015-06-01],
[2015-07-01],
[2015-08-01],
[2015-09-01],
[2015-10-01],
[2015-11-01],
[2015-12-01])
) as Piv

[/sql]

As you can see below, this query now only returns 3 rows but has all the same data. The values from column Collection_Date are now column headers and the data that was previously displayed in the row for each of these Collection_Date values in now displayed in the column.

t-sql query output using pivot operator

 

Limitations of PIVOT

The IN clause will only accept a static list of values. (Known as spreading values). You cannot use a subquery as an input so you need to know the values you want to pivot on. This can be worked around using Dynamic SQL however this takes a bit more effort.

Only one aggregate function can be used per PIVOT operator. – This cannot be COUNT(*)

Benefits of PIVOT

It should be easy to see at a glance how your data is trending. In this scenario, I want to see the data size for each table in each month. This shows me my rate of growth.

Note: In reality, this table could also be enhanced by storing other useful data such as row count, ReservedKB and UnusedKB however the point of the example was to show a simple PIVOT query in T-SQL.

 

Share this:

  • Tweet
  • Email

Related

Filed Under: front-page, T-SQL Tagged With: aggregation, grouping, How to write a PIVOT query in T-SQL, PIVOT, PIVOT Operator, spreading, SQLNEWBLOGGER, t-sql, T-SQL PIVOT, t-sql scripts

About John McCormack

John McCormack is an experienced SQL DBA with extensive knowledge of the two largest public clouds: AWS and Azure.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.