In 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.
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.
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.
Leave a Reply