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: