TSQL Tutorial 26: Pivot and Unpivot

 

Hello, I’m briefly going to discuss one topic that can easily throw you under the bridge if you did not take the time to actually practice it. Pivot and unpivot is not your regular every day TSQL term but its good to know it and use it when needed. We are going to take baby steps here to understand it from the very basic.

In SQL, a pivot table is a set of data that is transformed from a collection of separate rows to a collection of columns.
Pivot tables can be used to simplify extensive data in order to make it easier to read and understand. Lets take a look at a salestable

saleid	product	customer	salesdate	amount
1	laptop	cosby	2008	100
2	camera	kofi	2009	90
3	phone	cosby	2008	80
4	ipad	vince	2010	60

In a regular SQL, you find total sales to each customer each year like this

SELECT customer, salesdate, Sum(amount)

FROM salestable

GROUP BY salesdate, customer

that will give you the result below

customer    salesdate        amount

cosby          2008           $180

kofi                2009           $ 90

Vince            2011            $ 60

Now, what about if l want a result like below

customer     laptop              camera     phone     Ipad

cosby             $80                  $20           $60          $ 30

kofi                $ 70                  $10            $40          $5

This result displays each item l have in the store at the top, then in each record, it shows how much a customer spent in total on each item in my store.  Lets say this customer buys all these items on daily basis.  For example, a production company selling to retailers.

what about if l want a result like below also

customer         2008         2009       2010

cosby                 $180         $200        $300

vince                  $40            $90          $500

This result shows each customer and how much they spent in my store each year. This are different views of the same table and this result actually looks easier to comprehend, this is the idea of the pivot table.

To create a pivot table, an aggregate is used against a set of data to distribute multiple rows of a single column into a single row with multiple columns. This essentially pivots the result set sideways. This is very good especially for reporting purposes because it makes it easy for people to compare what is going on their business.

Lets see how we can achieve the first result using TSQL.

SELECT Customer, Laptop, Camera, Phone, Ipad
FROM(
SELECT Customer,Product,Amount
FROM salestable) a
PIVOT(
SUM(Amount)
FOR Product  in ( Laptop, Camera, Phone, Ipad)
) b

What is going on here?

Easy money, in the first statement we select the headers we want for the result from a derived table that gave us the customer, product and amount. It is recommend to name the derived table. I hope you understand a derived table, it’s simple a select statement that is given an alias name and can now act as a table for all kinds of TSQL table operations. If you have any doubt about derived table, please search for it right here on the blog, there is tutorial on it. Ok, so after we select the columns we want for our headers, we use the keyword PIVOT and put into bracket our aggregation function and the keyword FOR, which first specify the column in the derived table whose rows of record are now distributed into multiple columns. Then comes the IN operator which is use for searching in a list of items and a bracket containing all the columns we created using the single column from the original table, and this entire pivot statement is also give an alias b. This definition is rather long but l hope you understand the meat of the whole game.

Lets see how we can accomplish the second result

SELECT customer,[2008],[2009],[2010]
FROM(
SELECT customer,salesdate,amount
FROM salestable) a
PIVOT(
SUM(amount)
FOR salesdate in ([2008],[2009],[2010])
) b

The same logic applies here. so to make things very simple for us, let’s come up with a syntax that will help us on daily basis

SELECT <columns to show in the pivot table>

FROM( SELECT <columns> FROM <table>   ) AS  ‘derived table’

PIVOT(   Aggregate function(<column in derived table to aggregate>)

FOR   <column in derived table to group by> IN (<columns in header of result created from a single column in derived table>)

) As ‘Pivot derived table’

ORDER BY <pivot derived table>.<column> —optional step for ordering the records.

Try this at home, its good when you practice it. Here is the complete code, you can copy and paste in Microsoft SQL Server Management Studio and execute to see the results.

–Create a table

CREATE TABLE salestable(salesid int identity, product nvarchar(100) not null,customer nvarchar(100) not null ,salesdate date, amount money not null)

GO

–Put some records into the table

INSERT INTO salestable(product, customer, salesdate, amount)
values

(‘Laptop’,’Bush’,’2008-4-10′,100),
(‘Camera’,’Clinton’,’2009-2-1′,90),
(‘Phone’,’Obama’,’2008-1-1′,80),
(‘Ipad’,’Atta’,’2010-9-1′,60),

(‘Camera’,’Bush’,’2008-4-10′,100),
(‘Laptop’,’Clinton’,’2009-2-1′,90),
(‘Phone’,’Obama’,’2008-1-1′,80),
(‘Camera’,’Atta’,’2010-9-1′,60),

(‘Ipad’,’Bush’,’2008-4-10′,100),
(‘Phone’,’Clinton’,’2009-2-1′,90),
(‘Ipad’,’Obama’,’2008-1-1′,80),
(‘Phone’,’Atta’,’2010-9-1′,60),

(‘Phone’,’Bush’,’2008-4-10′,100),
(‘Ipad’,’Clinton’,’2009-2-1′,90),
(‘Laptop’,’Obama’,’2008-1-1′,80),
(‘Laptop’,’Atta’,’2010-9-1′,60)

— Query to see customers and total sales made each year

SELECT customer, YEAR(salesdate) AS ‘Year’, Sum(amount)

FROM salestable

GROUP BY YEAR(salesdate), customer

–Pivot table sample 1

SELECT customer,laptop,camera,phone,Ipad
FROM(
SELECT customer,product,amount
FROM salestable) a
PIVOT(
SUM(amount)
FOR product in (laptop,camera,phone,Ipad)
) b

–Pivot table sample 2

SELECT customer,[2008],[2009],[2010]
FROM(
SELECT customer,YEAR(salesdate),amount
FROM salestable) a
PIVOT(
SUM(amount)
FOR YEAR(salesdate) in ([2008],[2009],[2010])
) b

Now our next friend is Unpivot. This is basically the reverse of the pivot table, that means, assume we have stored the result set we generated using our examples above in a database and now, we are interested in generating the original table out of the pivoted table.

I always feel this is more complex than generating the pivot table because, in pivot table, we have the details and we simply aggregate to get the result the way we want it. But all the same, we have to know how to do this unpivot thing too. Before we go into this, there is a trick part to this, the unpivot is not going to get the exact table back as the original because when we perform the pivot operation, there is aggregation and merge going on and it’s not possible to retrieve every detail back. So let’s try this,

Now lets assume we store the result set we generated in the first case in the database in a table called pivotedtable, l’m talking about the one below.

customer     laptop              camera     phone     Ipad

cosby             80                   20            60            30

kofi                 70                   10             40           5

Now, how can we unpivot this result so that we go back to the original like below

Customer    Product     Salesdate                 Amount

Cosby            Laptop       2008-1-11              10

Cosby            Laptop      2008-2-10                70

Cosby           camera     2011-2- 11                60

I hope you can see the problem we are facing here. how do we get the details of everything? This is why l said earlier unpivot will not give us exactly the original table but it will try to give us as much as it can. The syntax for both pivot and unpivot are very similar.

Let’s try to unpivot the result and explain what is going on. Since we don’t have any date information, we’ll leave out the salesdate.

SELECT customer, Product,Amount
FROM(SELECT Customer, Laptop, Camera, Phone, Ipad
FROM pivotedtable ) pvt
UNPIVOT(Amount FOR Product IN (Laptop, Camera, Phone, Ipad )
) unpvt

we can see  the syntax is almost the same.  first, we select columns to see from a derived table that does not actually contain all of them. unpivot it to get the rest of the columns we are selecting in the header using Amount and FOR keyword on our Product Column to specify which columns in the derived table we are interested in getting information from.

Practice it at home. if you want to create the pivottedtable l’m using above, just do it like this

SELECT customer,laptop,camera,phone,Ipad
INTO pivottedtable
FROM(
SELECT customer,product,amount
FROM salestable) a
PIVOT(
SUM(amount)
FOR product in (laptop,camera,phone,Ipad)
) b

Selecting into will create the table for you and store the result in it on the fly. after that, write the unpivot query to see the result.

In the next tutorials, l’ll be doing lot of work on differences between different database objects. Advantages and disadvantages and limitations. All corrections, questions and feedback welcome. Stay blessed. Peace.