How to use unpivot for multiple columns with constants/missing column?

Question

I'm trying to use the UNPIVOT in order to avoid the old-fashioned way of multiple unions

The rationale for this is I'm collecting some data (lab test, result, unit, ...) I'd like to display in a appropriate way for reviewing them

Let's say, for example, my raw data looks like this:

ID, TESTA, RESULTA, UNITA, TESTB, RESULTB, UNITB, OTHER_UNITB
1,  'T1',  10,      1,     'T2',  2.5,          , 'kg',
2,  'T1',  15,      1,     'T2',  1.5,     1,         ,
3,  'T1',    ,       ,     'T2',  1,       1,

The following just works perfectly: for each ID, I retrieve 1 line per test with the corresponding result and unit:

select  id,
        property as test_code,
        decode(property, 'T1', 'Test 01', 'T2', 'Test 02', 'Unknown test') as test_name,
        result,
        unit
from    my_table
unpivot include nulls
        (
            (result, unit)
            for property in (
              (resulta, unita) as 'T1',
              (resultb, unitb) as 'T2'
            )
        )
;

However, things goes wrong when I try to retrieve the 'other unit' that is specific to the test 'T2' (keep in mind it's an example, I've got plenty of tests, almost 20)

I tried this:

select  id,
        property as test_code,
        decode(property, 'T1', 'Test 01', 'T2', 'Test 02', 'Unknown test') as test_name,
        result,
        unit,
        other_unit
from    my_table
unpivot include nulls
        (
            (result, unit, other_unit)
            for property in (
              (resulta, unita, null) as 'T1',
              (resultb, unitb, other_unitb) as 'T2'
            )
        )
;

It fails with message "Invalid identifier" for the 'null' statement I put in the unpivot.

I tried also to use a constant, in such way:

....
unpivot include nulls
        (
            (result, unit, other_unit)
            for property in (
              (resulta, unita, 0) as 'T1',
              (resultb, unitb, other_unitb) as 'T2'
            )
        )
;

And that fails too.

I'm stuck here and cannot figure out how to fix that without re-writting everything is a list of union statements - that I'd like to avoid at all cost because that's pretty complex to maintain:

select resulta as result,
       unita as unit,
       null as other_unit
from   my_table
union
select resultb as result,
       unitb as unit,
       other_unitbas other_unit
from   my_table
union
...

I also found a ugly solution:

select  id,
        property as test_code,
        decode(property, 'T1', 'Test 01', 'T2', 'Test 02', 'Unknown test') as test_name,
        result,
        unit,
        other_unit
from    (
            select  m.*,
                    null as null_item
            from    my_table m
        )

unpivot include nulls
        (
            (result, unit, other_unit)
            for property in (
              (resulta, unita, null_item) as 'T1',
              (resultb, unitb, other_unitb) as 'T2'
            )
        )
;

But honestly, I'm ashamed to do that!

Thanks by advance for any support

Why are you ashamed of the "ugly" solution? The additional column you generate is appropriately named and the query is clear what it does; there is nothing to be ashamed of, it isn't ugly and you should use it. — MT0, Sep 18 '19 at 11:42
My main concern is I'll have to create as many "pseudo columns" as I need for constant values. That works, I was just wondering if there is a way to write the default values/null values inside the unpivot clause — Julien, Sep 18 '19 at 12:21

Dr Y Wit · Answer 1 · 2019-09-18T13:04:38.900

You can check syntax diagram for unpivot clause.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#CHDJBHHI

It clearly shows that only columns may appear in a list (not literals or expressions).

You may need to create as many constant columns in inline view as many distinct constant values you require.

As an alternative approach, you can use old-school method: cross join + decode.

select id,
       test_code,
       decode(test_code, 'T1', 'Test 01', 'T2', 'Test 02', 'Unknown test') as test_name,
       decode(test_code, 'T1', resulta, 'T2', resultb) result,
       decode(test_code, 'T1', unita, 'T2', unitb) unit,
       decode(test_code, 'T2', other_unitb) other_unit
  from my_table t,
       (select 'T' || level test_code from dual connect by level <= 2)

Thanks, at least that clarifies what cannot be done in my case ! — Julien, Sep 18 '19 at 13:18

How to use unpivot for multiple columns with constants/missing column?

1 Answers1