PIVOT & UN PIVOT Tables in SQL Server

PIVOT & UN PIVOT Tables in SQL Server

The PIVOT operator, which was introduced in SQL Server 2005.
A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
Pivot tables are easily created by MSOffice users using in Excel.
The Pivot table enables report builders and BI specialists empower their presentation of reports and increase the visibility and understand ability of mined data, pivot tables are common and preferred widely.
Note: – When PIVOT and UNPIVOT are using the, the compatibility level of the database must be set to 90 or higher.
Check it: – sp_dbcmptlevel ‘database name’
Example:-
CREATE TABLE Ledger(Customer VARCHAR(25), Denomination VARCHAR(20), Quantity INT)
GO
— Inserting Data into Table
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Tom’,’10 £’,39)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Tom’,’50 £’,61)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Tom’,‘100 £’,17)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Scott’,’10 £’,44)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Scott’,’50 £’,50)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Scott’,‘100 £’,11)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Iain’,’10 £’,24)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Iain’,’50 £’,12)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Iain’,‘100 £’,22)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Anke’,’10 £’,30)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Anke’,’50 £’,45)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Anke’,‘100 £’,65)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Johan’,’10 £’,40)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Johan’,’50 £’,65)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Johan’,‘100 £’,45)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Gordon’,’10 £’,33)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Gordon’,’50 £’,44)
INSERT INTO Ledger(Customer, Denomination, quantity) VALUES(‘Gordon’,‘100 £’,66)
Select * from Ledger
SELECT * FROM Ledger
PIVOT (SUM(quantity) For Denomination IN ([10 £],[50 £],[100 £])) AS Total
Drop table Ledger
Unpivot table in sql server
Example:-
Create Table ledger1 (Id Int Identity(1,1),customer Varchar(100),Denomination1 Int,Denomination2 Int,Denomination3 Int)
Insert Into ledger1 values (‘Tom’, 180, 750, 600)
Insert Into ledger1 values (‘Iain’, 150, 950, 450)
Insert Into ledger1 values (‘Scott’, 860, 650, 300)
Insert Into ledger1 values (‘Gordon’, 600, 888, 700)
Select customer,Details,Denomination From
   (Select * From ledger1)
   As Result
   Unpivot
   (Denomination For Details In (Denomination1, Denomination2, Denomination3))
   As UnPvt
Output:
Read More:
content writing training
About Transaction Log with Example
Online MS SQL DBA training
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

SQL Server Error Logs

SQL Server Error Logs Detailed Information

SQL Server Error Logs * Error Logs maintains events raised by SQL Server server or …

Leave a Reply

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

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe