If you are looking for a PIVOT query (or called CROSS TAB query), and annoyed with the database provider for the lack of build-in pivot function, and you don't want a
complicated solution, you may consider this suggestion.
Let's begin from an example. You got four categories of food in the CATEGORY Table.
Category
--------
Meat
Fish
Vegetables
Cerial
There are four transactions and you has not sold any Vegetables yet.
SalesOrder No. Catgory Quantity Amount
--------------- -------- --------- --------
0001 Fish 2 10
0002 Meat 1 6
0003 Fish 1 5
0004 Cereal 5 50
You wanna generate a report to show the number of transaction by Categroy like this:
Category TxnCount
-------- ----------
Meat 1
Fish 2
Vegetables 0
Cereal 1
To form a pivot query with a single SQL92 compliant statement, first you need to construct the Cartesian product of the two (or more) table(s).
Second and last, numeric value 1 is assigned to the summarized field if the keys fields are exactly matched, otherwise, numeric value 0 is assigned. Simple enough! But there is no free lunch in this world, what you trade off is the memory space in this query. So, consider the data volumn size of the Cartesian product before you apply this approach!
[T-SQL version]
select a.Category, TxnCount=SUM(CASE WHEN a.Category = b.Category THEN 1 ELSE 0 END)
from Category a, SalesOrder b
A minor variation of the query can sum up the Amount by Catgory.
[T-SQL version]
select a.Category, TxnCount=SUM(CASE WHEN a.Category = b.Category THEN b.Amount ELSE 0 END)
from Category a, SalesOrder b
If you need to summerize by Month, get the sharp idea of the
Rozenshtein Method.
