I'm looking for a function that can output the following:
Total Sales Product_0 Product_1 Product_2 Other
Jan 5000 1000 1000 1000 2000
Feb 6000 2000 500 500 3000
Mar 3000 1000 700 200 1100
...
Basically, I would like to split a column into horizontal groups using a select/case/switch style statement.
It might look something like (even without the SUMSPLIT function I'm not sure if the query is correct but maybe you'll get the idea...) the following assuming sales order line table (so_line) has columns for order date (orderdate), the price to be paid for that line (totalprice), and a reference to a particular item (fk_item):
SELECT
to_char(datetime(so_line.orderdate),'MMM'),
SUM(so_line.totalprice),
SUMSPLIT(so_line.totalprice, so_line.fk_item = 0, so_line.fk_item = 1, so_line.fk_item = 2, else)
FROM
so_line
WHERE
...
GROUP BY
extract(month from so_line.orderdate)
ORDER BY
extract(month from so_line.orderdate) ASC;
Essentially, I'd like to have SUMSPLIT defined as:
The first value is the column that will be summed.
All other arguments are the conditions for creating a new column in the output (which will
obey grouping order by resetting the running sum totals for each split column created).
'else' can be optionally specified to indicate that anything not matching any of the conditions
will get dumped there.
OTHER POSSIBILITIES:
- Perhaps it should also take as an argument for each condition, the column name to be displayed.
- If there is a SUMSPLIT then there should also be COUNTSPLIT, AVGSPLIT, etc...
Does anybody else have any suggestions on how to accomplish a similar result without creating a new function?
If not, I would appreciate any pointers and advice on how this could be accomplished as I may start investigating how to actually implement these functions.
Patrick
