SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

oraclepivot

This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this works.

Essentially, I have a series of forms in a project, all of which have a status assigned to them. The statuses have an assigned order of precedence, which will trickle up to the higher level visits. The way this is currently working uses the following sql statement:

select state_code
 from (select ' || v_priorstr || ' 
    from m_object mo, m_form mf
      where mo.id = mf.id
        and mf.formlayout_id = :1
        and mo.v = :2)
 unpivot
   (
    state_counts
    for state_code in (' || v_priorstr || ')
   ) where state_counts = 1 and rownum = 1

where v_priorstr is a comma separated string of the statuses in order of their precedence. As an example, consider the following string: "Blank,Error,InProgress,Completed". The m_object table contains each of these columns, with either a 0 or 1 to show which statuses apply. (Forms can have more than one status) This query selects the highest priority status that it will then use to display a status icon for that form.

The problem arises when dealing with repeating forms (which can have multiple iterations of themselves under one link). They all share a single status icon in the navigation system.

While normally this works fine, since SQL will naturally display columns in the order you request them, and they maintain that order when unpivoted. However, with the multiple forms, and thus multiple rows, all that statuses of the first form/row take absolute precedence over the statuses of all the forms.

For example, if I have two of these repeating forms, the first of which is 'Completed' the second is 'Error', the above query (sans the rownum = 1) will return the following two rows:

Completed
Error

I have thus far attempted the following solutions:

Group By without an Order By throws the order off.
I can't use a case-based Order By because the status names can change.

In short, I need it to either sort by or group by the status names, in the order that is passed in via that string.

Best Answer

This uses the same basic technique as jonearles (+1) (since removed) but eliminates the PIVOT and would require only one function that turns the string into a CASE statement.

SELECT substr(
      MIN (
         CASE WHEN Blank=1 THEN '1 Blank' 
              WHEN Error=1 THEN '2 Error' 
              WHEN InProgress=1 THEN '3 InProgress' 
              WHEN Completed=1 THEN '4 Completed' 
         END
         )
   ,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;

I assume the data looks something like this:

drop table m_object;
create table m_object as 
   (select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);

drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);

Here is what the function might look like if it were done in PL/SQL. It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.

set serveroutput on format wrapped

DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';

   Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
      vBuilt     Varchar2(500);
      vWord      Varchar2(100);
      vChar      Char(1);
      vWordCount Number(1) := 1;
   Begin
      For vLoop In 1..Length(pOriginal) Loop
         vChar := substr(pOriginal,vLoop,1);
         If (vChar = ',') Then
            If (vBuilt IS NULL) Then
               vBuilt := 'CASE WHEN ';
            End If;
            vBuilt := vBuilt || vWord || '=1 THEN '''
               || to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
            vWord := '';
            vWordCount := vWordCount + 1;            
         Else                  
            vWord := vWord || vChar;            
         End If;
      End Loop;
      vBuilt := vBuilt || vWord || '=1 THEN ''' 
         || to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
      vBuilt := vBuilt || ' END ';
      Return vBuilt;
   End;

BEGIN
   vPassed := MakeCase(vPassed);
   DBMS_Output.Put_Line(vPassed);

   --Use vPassed here to build SQL statement.
END;
/
Related Question