Wednesday 2 April 2014

Pivot Table with Grand Total in SQL

At first crerate a table By following query


CREATE TABLE [dbo].[tblProduct](
          [Vendor] [varchar](50) NULL,
          [Product] [varchar](50) NULL,
          [TotalSell] [int] NULL
)

Insert records in Table by this queries:


INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Dell', N'LED', 50)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Dell', N'Mouse', 10)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'Processor', 101)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'MotherBoard', 50)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'cabinet', 12)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'Keyboard', 60)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Microsoft', N'OS', 1005)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'OS', 20)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Microsoft', N'Mouse', 100)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'LED', 10)
GO
INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'cabinet', 20)
GO



Now You can get result with sell count of each item by its vendor with Total and Grand Total



SELECT Vendor, ISNULL(LED,0) AS LED,ISNULL(Mouse,0) AS Mouse,
                    ISNULL(Processor,0) AS Processor,ISNULL(MotherBoard,0) AS MotherBoard,
                    ISNULL(cabinet,0) AS cabinet,ISNULL(Keyboard,0) AS Keyboard,
                    ISNULL(OS,0) AS OS, Total
FROM
(
          SELECT *, SUM(TotalSell) OVER (PARTITION BY Vendor) AS Total FROM tblProduct
)a
PIVOT
  (SUM(TotalSell) FOR Product IN (LED,Mouse,Processor,MotherBoard,cabinet,Keyboard,OS)
)b

UNION

SELECT 'Grand Total', SUM(ISNULL(LED,0)) AS LED, SUM(ISNULL(Mouse,0)) AS Mouse,
                            SUM(ISNULL(Processor,0)) AS Processor,
                            SUM(ISNULL(MotherBoard,0)) AS MotherBoard,
                            SUM(ISNULL(cabinet,0)) AS cabinet,
                            SUM(ISNULL(Keyboard,0)) AS Keyboard,
                            SUM(ISNULL(OS,0)) AS OS,SUM(Total) as Total
FROM
(
          SELECT *, SUM(TotalSell) OVER (PARTITION BY Vendor) AS Total FROM tblProduct
)a
PIVOT
          (SUM(TotalSell) FOR Product IN (LED,Mouse,Processor,MotherBoard,cabinet,Keyboard,OS)
)b

ORDER BY Total


Record will like below:


No comments:

Post a Comment