I have a pairs of related rows in my table.
How to merge these rows:
date col1 col2
2012-09-11 13:28:21.0000000 A 50
2012-09-11 13:28:21.0000000 A -50
to one row
date col1 col2 col3
2012-09-11 13:28:21.0000000 A 50 -50
if there can be a small difference between two dates (about one seconds and it occurs in 1 of 100 pairs only)? e.g.:
2012-09-11 13:28:21.0000000
2012-09-11 13:28:22.0000000
or in worse case, one second change whole minute:
2012-09-11 13:28:59.0000000
2012-09-11 13:29:00.0000000
update (string column):
how to merge the same lines with an additional col3 with string values?
date col1 col2 col4
2012-09-11 13:28:21.0000000 A 50 abc
2012-09-11 13:28:21.0000000 A -50 def
to:
date col1 col2 col3 col5 col6
2012-09-11 13:28:21.0000000 A 50 -50 abc def
or to:
date col1 col2 col3 col5
2012-09-11 13:28:21.0000000 A 50 -50 abc,def
solution (string) (extension of hkutluays answer):
max(case when col2 > 0 then col4 end) col5
max(case when col2 < 0 then col4 end) col6
Not tested but may solve the problem.
select
round(sysdate,'MI'),col1, sum( case when col2> 0 then col2 else 0 end ) col2,
sum( case when col2 < 0 then col2 else 0 end ) col3
from table
group by round(sysdate,'MI'),col1