'Pivot table sum on condition
One of the stored procedure in my project is giving the following data which has been stored in a table "tblSourceData":
Month MonthNo Year Type Supplier Name SupplierType Count
--------------------------------------------------------------------------------------
April 4 2021 Pending Supplier-1 Shipping Line 1
May 5 2021 Contracted Supplier-1 Shipping Line 4
June 6 2021 Contracted Supplier-1 Shipping Line 1
July 7 2021 Contracted Supplier-1 Shipping Line 1
August 8 2021 Contracted Supplier-1 Shipping Line 2
September 9 2021 Contracted Supplier-1 Shipping Line 3
October 10 2021 Contracted Supplier-1 Shipping Line 1
November 11 2021 Contracted Supplier-1 Shipping Line 1
November 11 2021 Contracted Supplier-2 Shipping Line 2
November 11 2021 Pending Supplier-2 Shipping Line 1
December 12 2021 Contracted Supplier-1 Shipping Line 1
December 12 2021 Contracted Supplier-2 Shipping Line 2
December 12 2021 Pending Supplier-2 Shipping Line 2
The 3 possible values for column "Type" above are "Contracted", "NonContracted" and "Pending".
I want to product the following output:
Supplier Name SupplierType Month Year Contracted Non-Contracted Pending Total
---------------------------------------------------------------------------------------------
Supplier-1 Shipping Line April 2021 0 0 1 1
Supplier-1 Shipping Line May 2021 4 0 0 4
Supplier-1 Shipping Line June 2021 1 0 0 1
Supplier-1 Shipping Line July 2021 1 0 0 1
Supplier-1 Shipping Line August 2021 2 0 0 2
Supplier-1 Shipping Line September 2021 3 0 0 3
Supplier-1 Shipping Line October 2021 1 0 0 1
Supplier-1 Shipping Line November 2021 1 0 0 1
Supplier-2 Shipping Line November 2021 2 0 1 3
Supplier-1 Shipping Line December 2021 1 0 0 1
Supplier-2 Shipping Line December 2021 2 0 2 4
---------------------------------------------------------------------------------------------
Grand Total 18 0 4 22
---------------------------------------------------------------------------------------------
I tried the following query but it is not producing the desired output:
SELECT [Supplier Name],SupplierType, [Month], [Year],
[Contracted]=isnull([Contracted],0), [NonContracted]=isnull([NonContracted],0), [Pending]=isnull([Pending],0)
FROM
(select [Supplier Name],SupplierType, [Month],MonthNo, [Year],[Type],[Count] from tblSourceData) AS tab
PIVOT
(
sum([Count])
FOR [Type] IN ([Contracted],[NonContracted],[Pending])
) AS Tab2
order by MonthNo
It is giving the following output:
Supplier Name SupplierType Month Year Contracted Non-Contracted Pending Total
---------------------------------------------------------------------------------------------
Supplier-1 Shipping Line April 2021 0 0 0 0
Supplier-1 Shipping Line May 2021 0 0 0 0
Supplier-1 Shipping Line June 2021 0 0 0 0
Supplier-1 Shipping Line July 2021 0 0 0 0
Supplier-1 Shipping Line August 2021 0 0 0 0
Supplier-1 Shipping Line September 2021 0 0 0 0
Supplier-1 Shipping Line October 2021 0 0 0 0
Supplier-1 Shipping Line November 2021 0 0 0 0
Supplier-2 Shipping Line November 2021 0 0 0 0
Supplier-1 Shipping Line December 2021 0 0 0 0
Supplier-2 Shipping Line December 2021 0 0 0 0
---------------------------------------------------------------------------------------------
Grand Total 0 0 0 0
---------------------------------------------------------------------------------------------
The information above may have been misaligned. Pardon me on this.
Any help on this will be appreciated.
EDIT: I have replaced the images of the data by its text representation.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|