SQL Server: Generate Report using PIVOT

In this blog I am going to show how we can generate a report using Pivot in SQL Server:

I want to dedicate this post to two of my friends cum brother Abhishek Nigam & Yogesh Gupta.

Abhishek Nigam & Yogesh Gupta If you see this post then I want to remember that beautiful day with both of you.

I got a business requirement to generate a report of Customer and their order detail by Month. Mean I have below 2 tables.

Customer:

  1. CREATE TABLE [dbo].[Customer](  
  2.     [Customer_ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Customer_Name] [varchar](50) NULL,  
  4.  CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED   
  5. (  
  6.     [Customer_ID] ASC  
  7. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  8. ON [PRIMARY]  
  9.   
  10. GO  
  11.   
  12. SET ANSI_PADDING OFF  
  13. GO  


  1. CREATE TABLE [dbo].[OrderDetails](  
  2.     [Order_ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Customer_Name] [varchar](50) NULL,  
  4.     [Unit] [intNULL,  
  5.     [Month] [varchar](50) NULL,  
  6.  CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [Order_ID] ASC  
  9. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  10. ON [PRIMARY]  
  11.   
  12. GO  
  13.   
  14. SET ANSI_PADDING OFF  
  15. GO  


Select Records From both Tables:



Now I want to see records by Month mean in which month which customer order how many unit.

To get this write below query using PIVOT syntax in SQL Server:
  1. SELECT * FROM (  
  2. SELECT c.Customer_Name, d.[Month],  
  3. ISNULL(d.Unit,0) AS Unit  
  4. FROm OrderDetails d RIGHT JOIN Customer c  on d.Customer_Name=c.Customer_Name  
  5. as s  
  6. PIVOT  
  7. (  
  8. SUM(Unit)  
  9. FOR [Monthin (January, February, March, April, May, June, July, August, September, October, November, December)  
  10. As Pivot1  
Ebook Download
View all
Learn
View all