How to Unfold the Results of an Oracle Query Based on the Value of a Column

How to unfold the results of an Oracle query based on the value of a column

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test ( col1, col2 ) AS
          SELECT 'a', 2 FROM DUAL
UNION ALL SELECT 'b', 3 FROM DUAL
UNION ALL SELECT 'c', 1 FROM DUAL

Query 1:

SELECT col1,
       col2
FROM   test t,
       TABLE(
         CAST( 
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= t.col2
           )
           AS SYS.ODCINUMBERLIST
         )
       )

Results:

| COL1 | COL2 |
|------|------|
|    a |    2 |
|    a |    2 |
|    b |    3 |
|    b |    3 |
|    b |    3 |
|    c |    1 |

Oracle query return one row based on column values

There is more than one way to approach this question. I prefer to explicitly join in the priorities, then use row_number() to choose the row that you want:

select app_name, name, role
from (SELECT APP_NAME, NAME, ROLE,
             ROW_NUMBER() over (partition by app_name order by priority) as seqnum
      FROM APP_PERSON p join
           (select 'Associate Director' as role, 1 as priority from dual union all
            select 'Director', 2 from dual union all
            select 'Executive Director', 3 from dual union all
            select 'VP', 4 from dual
           ) r
           on p.role = r.role
    ) t
WHERE APP_NAME='ABCD' and seqnum = 1
ORDER BY APP_NAME, NAME

Oracle remove rows from a query set based on sum

Here is one way to do it, using only analytic functions and aggregation. You didn't explain the AGE_IN_DAYS column in the output - based on your example, I assume it represents the age of the most recent of the positive rows preceding the last negative row.

The RUNNING_TOTAL column shouldn't exist in the inputs, since it is calculated from the other data. Even though you have it in the table, I ignore it - I compute it directly. (I assume what you show is not your real starting data, but the point where you weren't able to continue with your solution.)

There is also a mismatch between the example you used and your INSERT statements. I used the INSERT statements as they are (with a different value for one of the rows); this explains why my output looks different from yours.

The main trick is in the WITH clause, in the PREP subquery. I assign a flag to the rows after the last "negative" row. Then in the main query I group by this flag, and in addition to that, only when the flag is set, by AGE_IN_DAYS. This way all the rows up to and including the last "negative" one are in one group, while the remaining positive rows are one row per group. (I assume that AGE_IN_DAYS is distinct for each INVENTORY_ITEM_ID; if it isn't, I could use something else, ROWNUM for example - but then the problem wouldn't be well defined anyway.)

So, here it is. Please review and let me know if you have any questions.

with prep as (
  select inventory_item_id, type_qty, age_in_days,
         case count(case when type_qty < 0 then 1 end) 
              over (partition by inventory_item_id order by age_in_days)
              when 0 then 'Y' end as past_last_negative
  from   omsinvdt_temp
)
select inventory_item_id, sum(type_qty) as type_qty,
       min(case when type_qty > 0 then age_in_days end) as age_in_days,
       sum(sum(type_qty)) over (partition by inventory_item_id
                                order by max(age_in_days)) as running_total
from   prep
group  by inventory_item_id,
          case past_last_negative when 'Y' then age_in_days end
order  by inventory_item_id, age_in_days desc
;

INVENTORY_ITEM_ID   TYPE_QTY AGE_IN_DAYS RUNNING_TOTAL
----------------- ---------- ----------- -------------
            35253         25           4           159
            35253        134           1           134
           266234          0           3             0

Oracle - split single row into multiple rows based on column value

Use a recursive sub-query factoring clause:

WITH split ( parent_item, child_item, lvl, quantity ) AS (
  SELECT parent_item, child_item, 1, quantity
  FROM   your_table
UNION ALL
  SELECT parent_item, child_item, lvl + 1, quantity
  FROM   split
  WHERE  lvl < quantity
)
SELECT parent_item, child_item, 1 As quantity
FROM   split;

Or you can use a correlated hierarchical query:

SELECT t.parent_item, t.child_item, 1 AS quantity
FROM   your_table t,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM DUAL
             CONNECT BY LEVEL <= t.quantity
           )
           AS SYS.ODCINUMBERLIST
         )
       ) l;

As for which is more performant - try benchmarking the different solutions as we cannot tell you what will be more performant on your system.

combine multiple rows result in single row based on one column value

Something like this usually helps; lines #1 - 7 represent your sample data. Code you need begins at line #8.

SQL> with test (seqnum, type, name) as
  2    (select 456, 'SH', 'Google2'      from dual union all
  3     select 456, 'CN', 'transwork'    from dual union all
  4     select 123, 'SH', 'partyshipper' from dual union all
  5     select 123, 'CN', 'consignee'    from dual union all
  6     select 999, 'XX', 'littlefoot'   from dual
  7    )
  8  select seqnum,
  9    max(case when type = 'CN' then name end) consigneename,
 10    max(case when type = 'SH' then name end) shipppername,
 11    max(case when type not in ('CN', 'SH') then name end) otherparty
 12  from test
 13  group by seqnum;

    SEQNUM CONSIGNEENAM SHIPPPERNAME OTHERPARTY
---------- ------------ ------------ ------------
       123 consignee    partyshipper
       999                           littlefoot
       456 transwork    Google2

SQL>

ORACLE - How to display 3 different values from one column

This is how we usually do it:

SQL> with temp (gbc, id, value) as
  2    -- sample data
  3    (select 'a', 1, 50 from dual union all
  4     select 'b', 1, 15 from dual union all
  5     select 'c', 1, 34 from dual union all
  6     --
  7     select 'a', 2, 30 from dual union all
  8     select 'b', 2, 10 from dual union all
  9     select 'c', 2, 30 from dual union all
 10     --
 11     select 'a', 3, 20 from dual union all
 12     select 'b', 3,  4 from dual union all
 13     select 'c', 3, 12 from dual
 14    )
 15  select gbc,
 16         sum(case when id = 1 then value end) as abc,
 17         sum(case when id = 2 then value end) as def,
 18         sum(case when id = 3 then value end) as ghi,
 19         sum(case when id = 4 then value end) as jkl
 20  from temp
 21  group by gbc;

GBC        ABC        DEF        GHI        JKL
--- ---------- ---------- ---------- ----------
a           50         30         20
b           15         10          4
c           34         30         12

SQL>

---

Or, if I understood your query, you'd just use it as a CTE and extract desired values:

with temp as
  (select 
      typeid,
      objectid
     ,trunc(datetime + 1 / (24 * 12), 'HH24') datetime
     ,timezone
     ,value as value
     ,loadid loadid
   from prod.ts_wsi_hourly_observation
   where typeid in(1,2,3,4)
  )
select sum(case when typeid = 1 then value end) as abc,  
       sum(case when typeid = 2 then value end) as def,
       sum(case when typeid = 3 then value end) as ghi,
       sum(case when typeid = 4 then value end) as jkl
from temp
group by objectid; 

SQL - How to select a row having a column with max value

Keywords like TOP, LIMIT, ROWNUM, ...etc are database dependent. Please read this article for more information.

http://en.wikipedia.org/wiki/Select_(SQL)#Result_limits

Oracle: ROWNUM could be used.

select * from (select * from table 
order by value desc, date_column) 
where rownum = 1;

Answering the question more specifically:

select high_val, my_key
from (select high_val, my_key
      from mytable
      where something = 'avalue'
      order by high_val desc)
where rownum <= 1

---

Related Topics