Make SQL Pivot Table
Pivot Table is result data table from transaction data is processed by Aggregate function (Ex. Sum, Avg, Max, Min) Pivot table is use for analyst each point of view. For easy to understand it. We see example now.
This structur table contains.
SellerName : Name of Seller.
InvoiceNO : No of invoice or bill.
InvoiceDate : Date of Invoice or bill.
Amount : Total circulation of Invoice.
Assume We have question is we look for sales volume of each seller and separate by month.
General query we maybe with WHERE MONTH(InvoiceDate) = 1 , WHERE MONTH(InvoiceDate) = 2 til complete And maybe use GROUP BY Together.
Example Query Month = 1 Only
SELECT SellerName,SUM(Amount) as Amount
FROM InvoiceList
WHERE
MONTH(InvoiceDate)=1
GROUP BY SellerName
View of report is not comfortable to analyst because we will use
several month table. For comfortable to analyst we should use PIVOT
Example Query Pivot
SELECT * FROM
(SELECT MONTH(InvoiceDate) as Month, Amount ,SellerName
FROM InvoiceList
) as SourceTable
PIVOT
(
SUM(Amount) for SellerName in (Dang,Dum,Jumpee,SreeDa)
) as PV
*** Next problem is null value on the table. Focus at month 1 of Dum It has Null because all of month 1 Dum not have circulation. If we use this query on program maybe found error. We can use ISNULL for improve our query.
SELECT Month
, ISNULL(Dang,0) as Dang
, ISNULL(Dum,0) as Dum
, ISNULL(Jumpee,0) as Jumpee
, ISNULL(SreeDa,0) as SreeDa
FROM
(SELECT MONTH(InvoiceDate) as Month, Amount ,SellerName
FROM InvoiceList
) as SourceTable
PIVOT
(
SUM(Amount) for SellerName in (Dang,Dum,Jumpee,SreeDa)
) as PV
After Improve query...
