Sunday , October 22 2017
Home / MSSQL DBA / PIVOT & UN PIVOT Tables in SQL Server

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 understandability 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:
The Secrets Of Rich And Famous Writers
About Transaction Log with Example
Are You Still Using That Slow, Old Typewriter?

Comments

comments

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 *