SQL fry: PIVOT and UNPIVOT

SQL-Tables
SQL-Tables

SQL fry: PIVOT and UNPIVOT

Welcome to CodeSpread!

What is PIVOT?

In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIVOT says “The central point, pin, or shaft on which a mechanism turns or oscillates.”

We just have to concentrate on two points of the above definition- a central point and a mechanism. These two points are required to define PIVOT in SQL server.

In SQL server, we have a Table like Transactions which records Customer,UnitsBought and AmountPaid. This table with sample data looks like

Transactions

When we are going to pivot this table, we require a ‘central point’ which for example we take as SUM(UnitsBought) and ‘a mechnaism’ which is the static values of Amount paid [10,100,150,500]. Now we just have to turn this mechanism on a central point. Lets see the query to do that and how the result looks like


SELECT * FROM transactions
PIVOT (SUM(UnitsBought) For AmountPaid IN ([500],[100],[150],[10])) AS EachAmountPaid

And the result of PIVOT operation will be

Pivoted

When we say static values of Amount paid [10,100,150,500], then we mean that the amount paid should not vary for each and every row ,as for that condition we will have to write a dynamic pivot query.

We stick with this simple example to grasp the concept. Also, we will keep the post short and cover UNPIVOT in subsequent post.

Author: sush

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • SQL PIVOT SlideShow and VideoSQL PIVOT SlideShow and VideoHere is a summary of SQL PIVOT query. This ppt is also available for download. You can also check the same ppt on our youtube channel. http://youtu.be/cHNMdX2r5w0
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • SQL : Transactions and ACID properties.SQL : Transactions and ACID properties.There are always two point of views of transactions. First, the user point of view, for example, customer went to an ATM to retrieve some amount. For him, the transaction might look like, Fo...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...
  • SQL: Usage of char, varchar and varchar(MAX)SQL: Usage of char, varchar and varchar(MAX)These are character data types in SQL. We will take an example and describe each type. Later we determine the cases where one of them can be applied. [code] Create Table testable( Name char(20...
  • Styles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptStyles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptDevelopers take months to write beautiful pieces of code and reviewer gets only few hours or I say, minutes to provide suggestions and improvisations. Then it becomes a hard deal for a developer to...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...