How To Code a Matrix Report in SQL

A matrix report is a grid-like representation of data. It has a row of labels, a column of labels and, in a grid, data related to both row and column. Matrix reports are also called crosstab reports.

A matrix report has one group of data for rows, one group of data for columns, one group of data cross-product and one group of data as the value for cross-product. The cross-product group represents all possible combinations of row and column groups.

Let's take a look at an example. Assume you have a table called Inv_summary. It has three columns: Inv_date, Inv_status and Inv_value. Now, you want to generate a report that shows total value of open and close invoices for each month, for example like below.

MONTH     OPEN       CLOSE

-------- ------------ ------------

Jan-07       20            19

Feb-07        0            153

Mar-07       18            56

Apr-07       43             0

May-07      93             0

Jun-07       79             0

Jul-07       112             0

Here are the steps to generate the report.

1. Create a table Inv_summary.

Create table Inv_summary ( Inv_date date, Inv_status varchar2(1), Inv_value number);

2. Insert some data into Inv_summary table.

Insert into Inv_summary values ('01-jan-07','O',20.00);
Insert into Inv_summary values ('01-jan-07','C',12.00);
Insert into Inv_summary values ('10-jan-07','C',7.00);
Insert into Inv_summary values ('05-feb-07','C',153.00);
Insert into Inv_summary values ('12-mar-07','C',48.00);
Insert into Inv_summary values ('15-mar-07','C',8.00);
Insert into Inv_summary values ('23-mar-07','O',18.00);
Insert into Inv_summary values ('20-apr-07','O',43.00);
Insert into Inv_summary values ('01-may-07','O',61.00);
Insert into Inv_summary values ('01-may-07','O',32.00);
Insert into Inv_summary values ('01-jun-07','O',18.00);
Insert into Inv_summary values ('01-jun-07','O',33.00);
Insert into Inv_summary values ('01-jun-07','O',28.00);
Insert into Inv_summary values ('01-jul-07','O',20.00);
Insert into Inv_summary values ('01-jul-07','O',24.00);
Insert into Inv_summary values ('01-jul-07','O',68.00);
Commit;

3. Run the following sql command to generate the report.

SELECT to_char(to_date(to_char(inv_date,'Mon-yy'),'Mon-yy'),'Mon-yy') Month,
sum(decode( inv_status, 'O', inv_value, 0 )) Open,
sum(decode( inv_status, 'C', inv_value, 0 )) Close
FROM Inv_summary
GROUP BY to_date(to_char(inv_date,'Mon-yy'),'Mon-yy')
ORDER BY to_date(to_char(inv_date,'Mon-yy'),'Mon-yy')

MONTH     OPEN       CLOSE

--------- ------------ ------------

Jan-07       20           19

Feb-07        0          153

Mar-07       18           56

Apr-07       43            0

May-07      93            0

Jun-07       79            0

Jul-07       112           0

In the above example, we have set it up so that the invoice_status group goes across and the month group goes down, forming a grid that shows total value of invoices. The key part in above SQL command is DECODE function. It converts rows into columns by checking the value of Inv_status column. You can write a matrix report with single or multiple queries. A single query report is generally more efficient than a multiple query.