Coding a Matrix Report in SQL

Coding a Matrix Report in SQL

Coding a Matrix Report in SQL : Multi-thousand tips to make your life easier.

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 as the cross-product, and one group of data as the value for the 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 the total value of open and closed 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 the 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 the total value of invoices. The key part in the above SQL command is the DECODE function. It converts rows into columns by checking the value of the 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.