Transposing data using UNPIVOT

A couple of years ago I posted a method to remove nulls from a report using the Value Attribute Paris - Column template.

Here's an example of how we might utilise the region, within the breadcrumb region position.

Note - some values may have been adjusted from this screenshot for their protection.

Any nulls were shown as a tilde, then hunted down and eliminated with some jQuery that executes after refresh of the region, and/or on load of the page

Cool idea, can be improved.

And when the JavaScript was placed on one line, it seems so innocuous. It works, so what? What's the harm?
$('dd.t-AVPList-value').each(function(){if ($(this).text().indexOf('~')>0) $(this).hide().prev().hide()})

The trouble with my original method is that it's executing jQuery after the page is rendered. This represents extra work that could be eliminated. The same justification was present in Oracle Forms, as Post-Query triggers were not preferred.

And of course the applies to DML being applied to the database. Sure, you may need some conditional processing and apply a zillion single updates, or you could write some elegant SQL to do it within one update.

The other problem is that the Universal Theme responds to this change with some content movement that can frustrate the user - the body bubbles up to meet the removed content.

New and Improved Solution

Side-note: how can it be new and improved?

In this case I've been talking about columns from a relative simple query, as simple as selecting from scott.emp.

If we can transpose these results, like a magic wand you can do in Excel, then we could just use the Value Attribute Pairs - Row version of the template, and not worry about any jQuery that manipulates the page after it's rendered.

We tranpose columns by surrounding the existing query with a simple UNPIVOT.

select * from (
    select to_char(empno) empno
     ,ename
     ,job
     ,to_char(sal) sal
     ,to_char(comm) comm
    from scott.emp
    where empno = 7788
    --where empno = 7654
) 
unpivot 
(val
 for name in (
    (empno) as 'Emp No'
   ,(ename) as 'Name'
   ,(job) as 'Job'
   ,(sal) as 'Sal'
   ,(comm) as 'Commission'
 )
);

NAME       VAL                                     
---------- --------
Emp No     7788                                    
Name       SCOTT                                   
Job        ANALYST                                
Sal        3000   

4 rows selected.

... query executed with other empno

NAME       VAL                                     
---------- ---------
Emp No     7654                                    
Name       MARTIN                                  
Job        SALESMAN                                
Sal        1250                                    
Commission 1400

5 rows selected.

Notice the result using an emp with a commission shows more rows. The page will only render the data supplied so there no dynamic action required.

Default ordering seems to honour the order of elements in the FOR expression.
Datatypes of columns must match up, hence the to_char around the numeric columns.

Update - I saw Mike on the forums suggest that "transpose" implies a matrix, and offered a combined unpivot/pivot.

tl;dr steps

• surround the existing query with unpivot
• add any datatype conversions necessary to make columns the same
• change report region from pairs with column to pairs with row
• remove declarative column ordering
• remove the dynamic action that finds the tildes

If you want to modify how something presents itself on an APEX page, there are other options to explore before jQuery

• Template options
• Conditional SQL, manifesting as HTML expression in a column
• CSS solutions trump JavaScript.

Simplify, man.