CODEWITHSUNDEEP
sqloracle-database
Ranjith Kumar
Ranjith Kumar

Reputation: 51

How to make a query to find this?

I have a data like this in a table:

column1   column2
a         1
a         2
b         2
b         3
a         4
c         5

I want a output like this:

column1 column2
a       1-2
b       2-3
a       4-0
c       5-0

Upvotes: 0

Views: 89

Answers (4)

wumpz
wumpz

Reputation: 9191

You should be carefull using something like 

 select column1, column2, rownum from table1

to create some unique ids for your data. Per definition is the ordering without an order by in your SQL not defined. Therefore it is by coincidence that 

select * from table1

returns your rows in the ordering you inserted it in your database. As the data grows you will get exceptions to this ordering. So it is highly recommended to put a primary key column in your data table to preserve this insert ordering. I included the column id for this.

Using this pimped dataset you could get the requested data using this query:

with data_aggr as (
select column1,
case 
  when lead(column1,1,' ') over (order by id)<>column1
  and lag(column1,1,' ') over (order by id)<>column1
  then column2 || '-0'
  when lead(column1,1,' ') over (order by id)=column1
  and lag(column1,1,' ') over (order by id)<>column1
  then column2 || '-' || lead(column2,1) over (order by id)
  else null
end aggr_col2
from table1)
select column1, aggr_col2 from data_aggr where not aggr_col2 is null

http://sqlfiddle.com/#!4/cd24d/19

Upvotes: 0

Hamidreza
Hamidreza

Reputation: 3128

Try this query:

with vw1 as 
(select table1.*,rownum rn from table1),
vw2 as (select col1,col2,rn,rn - col2 dis from vw1),
vw3 as (select col1,min(rn),to_char(min(col2))||' - '||
case when min(col2) = max(col2) then '0' else to_char(max(col2)) end col2 from vw2
group by col1,dis order by min(rn))
select col1,col2 from vw3;

SQL Fiddle

Upvotes: 2

Gooner
Gooner

Reputation: 256

Try this query...

select * from 
(
select col1 as column1,case when LEAD(col1 , 1, 0) OVER (ORDER BY col2) = col1
then concat( LEAD(col2 , 1, 0) OVER (ORDER BY col2),'-'||col2)   
else (case when lag(col1,1,0) over (ORDER BY col2) <> col1 then 
concat(col2,'-'||'0')else '0' end)
end as column2
from table
order by col2
)
where column2<>'0'
;

Upvotes: 0

MatBailie
MatBailie

Reputation: 86765

You haven't really given a lot of information.
- Will there always only be 1 or 2 values in column2, for each value in column1?
- Will column2 always be in order?
- etc, etc?

But, for the given data, the following should give the results you have asked for.

SELECT
  column1,
  MIN(column2)                                          AS first_column2,
  CASE WHEN COUNT(*) = 1 THEN 0 ELSE MAX(column2) END   AS final_column2
FROM
(
  SELECT
    ROW_NUMBER() OVER (                     ORDER BY column2, column1)  AS sequence_main,
    ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2         )  AS sequence_c1,
    *
  FROM
    your_table
)
  AS sequenced_table
GROUP BY
  sequence_main - sequence_c1,
  column1
ORDER BY
  MIN(sequence_main)


Example calculations:

column1   column2   |  sequence_main  sequence_c1  main - c1  |  group
a         1         |  1              1            0          |  a1
a         2         |  2              2            0          |  a1
b         2         |  3              1            2          |  b2
b         3         |  4              2            2          |  b2
a         4         |  5              3            2          |  a2
c         5         |  6              1            5          |  c5

Upvotes: 0

Related Questions