7.5 How do I…Simulate a cross-tab query using a view?Problem
In other applications such as Microsoft Access, I use a cross-tab query to view records horizontally. In my table, I have one record for each month. In my reports and inquiry screens, I want to display the data for a year horizontally, even though a record exists for each month. How do I simulate a cross-tab query using a view to present the combination of multiple records as a single row?
Technique
The DECODE function is a very powerful tool for creating advanced queries. DECODE works like an in-line IF…THEN…ELSE statement. It can be used to view data horizontally by returning a value if the row is in the correct column.
Steps
1. Run SQL*Plus and connect as the WAITE user account. The CHP7_15.SQL file, shown in Figure 7.8, builds a table that will be the basis for the cross-tab view and inserts sample data. The CROSS_TAB table contains one row for each year, month combination. The VALUE column represents the value for the given month.
2. Run the command to create the table and build sample data.
SQL> START CHP7_15.sql
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
3. Load CHP7_16.SQL into the SQL buffer. The file contains an SQL script to query the table created in the previous steps, displaying the data horizontally.
SQL> GET CHP7_16.sql
1 SELECT YEAR,
2 SUM(DECODE(month,1,VALUE,0)) January,
3 SUM(DECODE(month,2,VALUE,0)) February,
4 SUM(DECODE(month,3,VALUE,0)) March
5 FROM
6 CROSS_TAB07
7 GROUP BY YEAR
8* ORDER BY YEAR
Line 1 begins the query statement and selects the YEAR column. The YEAR column is the only column not modified by a GROUP BY operator. Lines 2 through 4 return the values for the first three months. The DECODE function returns the VALUE if the MONTH column is correct for the column, otherwise it returns 0. The SUM operator displays one row for each year. Because 0 will be returned for each column whose month is not correct, the SUM operator doesn’t affect the data besides grouping it as a single row. Line 7 groups the data by the YEAR column. Because the YEAR column retrieved in line 1 is not a GROUP BY expression, the GROUP BY clause in line 7 is required.
4. Run the query. Because sample data was created for two years, two rows will be returned by the query.
SQL> START CHP7_16.sql
YEAR
JANUARY
FEBRUARY
MARCH
--------- --------- --------- ---------
1997
235
180
185
1998
245
200
188
2 rows selected.
SQL>
5. Load CHP7_17.SQL into the SQL buffer. The file contains a CREATE VIEW statement to create the YEAR_VIEW07 view based on the query shown in Step 3.
SQL> GET CHP7_17.sql
1 CREATE VIEW YEAR_VIEW07
2 AS
3 SELECT YEAR,
4 SUM(DECODE(MONTH,1,VALUE,0)) January,
5 SUM(DECODE(MONTH,2,VALUE,0)) February,
6 SUM(DECODE(MONTH,3,VALUE,0)) March,
7 SUM(DECODE(MONTH,4,VALUE,0)) April,
8 SUM(DECODE(MONTH,5,VALUE,0)) May,
9 SUM(DECODE(MONTH,6,VALUE,0)) June,
10 SUM(DECODE(MONTH,7,VALUE,0)) July,
11 SUM(DECODE(MONTH,8,VALUE,0)) August,
12 SUM(DECODE(MONTH,9,VALUE,0)) September,
13 SUM(DECODE(MONTH,10,VALUE,0)) October,
14 SUM(DECODE(MONTH,11,VALUE,0)) November,
15 SUM(DECODE(MONTH,12,VALUE,0)) December,
16 SUM(VALUE) Total
17 FROM
18 CROSS_TAB07
19* GROUP BY YEAR
Lines 2 through 15 create a column for each month of the year. Line 16 creates a total for each year.
6. Run the statement to create the view.
SQL> /
View created.
7. Look at the description of the view created with the DESCRIBE statement. Figure 7.9 shows the results of the operation within SQL*Plus.
How It Works
The DECODE function works like an in-line IF statement and can be used within a query to present records horizontally. Steps 1 and 2 create the tables used in this How-To. Step 3 uses the DECODE function to transform data from a vertical view to a horizontal view. For each column, the VALUE field is only added to the column if the MONTH belongs in it. Steps 5 and 6 build a view that hides the complexity of the query from the user. Step 7 shows that the view represents the data as 12 columns instead of 12 separate rows.
Comments
The DECODE function technique uses a powerful feature of Oracle. Each time the view is queried, many more rows from the base tables are queried than are actually shown by the view. This may cause inadequate performance. Consider creating summary tables to represent data of this type if the performance of your views is unacceptable.