SQL: PIVOT & UNPIVOT

Instructor: David Gloag

David has over 40 years of industry experience in software development and information technology and a bachelor of computer science

SQL is an important tool for manipulating information in today's world. In this lesson, we'll take a look at SQL, and two especially useful commands from the language, PIVOT and UNPIVOT.

Making Decisions Based On Information

They say that information is power. If this is true, then the ability to manipulate that information would be even more powerful. Just ask BestBuy when they try to figure out what products to during the Christmas season, or Apple when they look at developing a new product, or Dunkin Donut's when they consider augmenting their list of available coffees. Information drives these decisions, and others like them. So it will come as no surprise that the information these organizations capture needs a little manipulation to get the results they are after. One of the ways they do that is through SQL.

What is SQL?

SQL, or structured query language as it is periodically known, is a command-based language that is used to manipulate information in a database. The language contains many commands, each performing a specific operation on the information. As an example, you can search, sort, retrieve, and store, as your needs require. SQL has been around for some time, having been developed in the early 70's by IBM. A few years later (1979), Oracle released it as a product. Today, many companies use it, and incorporate it into their product offerings, companies like Oracle, IBM, SyBase, Postgres, and Microsoft, to name a few.

What PIVOT in SQL?

PIVOT is a command in SQL that converts a table of information into a more consolidated version of that table. For example, if you had the following table of values:

Person Year Sales
Jack 2015 5000.00
Melinda 2016 6000.00
Jack 2016 3000.00
Jack 2015 1000.00
Melinda 2015 5000.00
Melinda 2016 1000.00

Then applying the PIVOT command in a specific fashion could give something that looks like:

Year Jack Melinda
2015 6000.00 5000.00
2016 3000.00 7000.00

The end result has changed the column headers, and compressed the information contained in the table by summing up entries that duplicated the name and year. Please note that this example summed the values for compressing them. The PIVOT command allows for other possibilities, which we'll see in a subsequent section.

What UNPIVOT in SQL?

UNPIVOT is a SQL command that undoes the effect of the PIVOT command, almost. We say almost because while it can put the columns in the new table back to rows, it cannot split up any aggregated results. It doesn't know how they were created, or where the information came from. For example, Jack's two entries in 2015, or Melinda's two entries in 2016. These values remain consolidated. So for the example above, the restored table would look like:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support