Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

SQL Server - Generate Report Using PIVOT

Article:
Viewed:  958 
Posted On:  06/05/2015 22:44:46 
How we can generate report in SQL Server using PIVOT ? 

 

In this article 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. J

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

Customer:

CREATE TABLE [dbo].[Customer](
          [Customer_ID] [int] IDENTITY(1,1) NOT NULL,
          [Customer_Name] [varchar](50) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
          [Customer_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON
PRIMARY]
) ON [PRIMARY]
GO 

SET ANSI_PADDING OFF
GO

 

1.png

Image 1.

CREATE TABLE [dbo].[OrderDetails](
          [Order_ID] [int] IDENTITY(1,1) NOT NULL,
          [Customer_Name] [varchar](50) NULL,
          [Unit] [int] NULL,
          [Month] [varchar](50) NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
          [Order_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON
PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 

2.png

Image 2.

Select Records From both Tables:

3.png

Image 3.

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:

SELECT * FROM (
SELECT c.Customer_Name, d.[Month],
    ISNULL(d.Unit,0) AS Unit
    FROM OrderDetails d RIGHT JOIN Customer c  on d.Customer_Name=c.Customer_Name
) as s
PIVOT
(
    SUM(Unit)
    FOR [Month] in (January, February, March, April, May, June, July, August, September, October, November, December)
) As Pivot1
 

4.png

Image 4.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us