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