Oracle Sql- Adding column to pivot result

I’m have an issue that I’m not entirely sure how to fix. So I have a table of the format

ID | ORDER_TYPE | VALUE | UNITS
0  |  SODIUM    |   12  | mEq/L
0  |  SODIUM    |   35  | mEq/L
0  |  BUN       |   9   | mg/dL
1  |  SODIUM    |   17  | mmol/L
1  |  SODIUM    |   28  | mmol/L
1  |  BUN       |   8.4 | mg/dL
...

Where order_type is the column in question I use to pivot. Value is the column I’m using to run aggregates, but the problem is Units. So for each unique ID + order_type combo, units will be the same. But I’m trying to find a way to include it in pivot so it’s included in the end result. Here’s the pivot I have (currently excluding units in my select…from statement):

pivot (
    max(VALUE) as max_val,
    min(VALUE) as min_val,
    avg(VALUE) as mean_val,
    stddev(VALUE) as stddev_val
    for ORDER_TYPE in ('BUN','CREATININE','SODIUM','POTASSIUM','HEMOGLOBIN','WBC COUNT','CHLORIDE','ALBUMIN','TROPONIN','BNP')
)

What I’m trying to do is have a column called units in the final output so I get something like

ID | SODIUM_MAX_VAL | SODIUM_MIN_VAL | ... | SODIUM_UNITS | ... | BUN_UNITS
0  |  35            |   12           | ... | mEq/L        | ... | mg/dL
1  |  28            |   17           | ... | mmol/L       | ... | mg/dL

So I know what the original units were measured in. I was thinking I could also leave it outside of pivot and add the column in at the end, but I can’t figure out a way to do that either. Units could also be null, but only if the value is null. Any help would be appreciated, thanks!

EDIT: Fixed an old title- don’t know why it was there

Answer

What does your title have to do with your question? (I will come back to that in a moment.)

Your pivot question is trivial. To the list of pivoted columns, add

min(UNITS) as units

– since the units are always the same for any (ID, ORDER_TYPE) pair, taking the min doesn’t actually do anything, but you must always aggregate values in a pivot clause (pivoting is, indeed, an aggregate operation, which can be easily replicated with a group by query).

Back to your title vs. your question. From the title, I thought you will ask how to avoid the quotes in the column names in the output. That is easy too. You are already giving aliases to the pivoted columns. You can (and almost always should) use aliases also in the in list. Something like this:

with
  inputs (id, order_type, value, units) as (
    select 0, 'SODIUM', 12  , 'mEq/L'  from dual union all
    select 0, 'SODIUM', 35  , 'mEq/L'  from dual union all
    select 0, 'BUN'   ,  9  , 'mg/dL'  from dual union all
    select 1, 'SODIUM', 17  , 'mmol/L' from dual union all
    select 1, 'SODIUM', 28  , 'mmol/L' from dual union all
    select 1, 'BUN'   ,  8.4, 'mg/dL'  from dual
  )
select *
from   inputs
pivot (
    max(VALUE)    as max_val,
    min(VALUE)    as min_val,
    avg(VALUE)    as mean_val,
    stddev(VALUE) as stddev_val,
    min(units)    as units
    for ORDER_TYPE in ('BUN' as bun, 'SODIUM' as sodium)
);

ID BUN_MAX_VAL BUN_MIN_VAL BUN_MEAN_VAL BUN_STDDEV_VAL BUN_UNITS SODIUM_MAX_VAL SODIUM_MIN_VAL SODIUM_MEAN_VAL SODIUM_STDDEV_VAL SODIUM_UNITS
-- ----------- ----------- ------------ -------------- --------- -------------- -------------- --------------- ----------------- ------------
 0           9           9            9              0 mg/dL                 35             12            23.5            16.263 mEq/L 
 1         8.4         8.4          8.4              0 mg/dL                 28             17            22.5             7.778 mmol/L