CODEWITHSUNDEEP
sqloracle11g
Water
Water

Reputation: 67

How to achive below output using sql query in oracle 11g.?

I have table with below data :

Block_id Value

1             5
2             5
3             5
4             0
5             0
6             4
7             4

And I have to write query that give me below output :

Block_id

   1-3
   6-7

How to achieve this with pl/sql in Oracle 11g ?

Upvotes: 0

Views: 101

Answers (3)

DazzaL
DazzaL

Reputation: 21973

you can do something like:

SQL> select * from data order by block_id;

  BLOCK_ID      VALUE
---------- ----------
         1          5
         2          5
         3          5
         4          0
         5          0
         6          4
         7          4
         9          5
        10          5
        12          2

SQL> select min(block_id) || '-' || max(block_id) block_range, value
  2    from (select block_id, value, max(grp) over (partition by value order by block_id) grp
  3            from (select block_id, value,
  4                         case
  5                           when lag(block_id) over (partition by value order by block_id) < block_id - 1
  6                           then
  7                             row_number() over (partition by value order by block_id)
  8                           when row_number() over (partition by value order by block_id) = 1 then 1
  9                           else null
 10                         end grp
 11                    from data
 12                   where value != 0))
 13   group by value, grp
 14   order by min(block_id);

BLOCK_RANG      VALUE
---------- ----------
1-3                 5
6-7                 4
9-10                5
12-12               2

Upvotes: 1

user1919238
user1919238

Reputation:

An alternative approach is to use aggregate functions:

select distinct
        min(block_id) over (partition by value) || '-' ||
        max(block_id) over (partition by value)
   from whatever_your_table_is_called
   where value > 0;

Upvotes: 0

mavroprovato
mavroprovato

Reputation: 8352

You do not need PL/SQL to do this, a simple query will do:

CREATE TABLE test(
    a   INTEGER,
    b   INTEGER
);

INSERT INTO test VALUES (1, 5);
INSERT INTO test VALUES (2, 5);
INSERT INTO test VALUES (3, 5);
INSERT INTO test VALUES (4, 0);
INSERT INTO test VALUES (5, 0);
INSERT INTO test VALUES (6, 4);
INSERT INTO test VALUES (7, 4);

select min(a) || '-' || max(a) from test group by b order by 1;

Upvotes: 0

Related Questions