Grouping between consecutive rows in a table without cursor

I trying to get a grouping done between 2 rows without a cursor, can some one help me reg this

Col1开发者_如何学Python(int)  Col2(int)
---------  ---------
1          20
2          30
3          40

I want output like this

Col1  Col2
----  ----
1-2   50
2-3   70

Are you sure you aren't missing any rows...

Select cast(a.col1 as varchar(10)) + '-' + cast(b.col1 as varchar(10)) as col1, 
 a.col2 + b.Col2 as Col2
From mytable a
  Inner Join mytable b on b.col1 = (a.col1 + 1)

if you might be missing rows, you might need to be more complicated.

That's a tricky one if you don't want to repeat the rows (1-2, 2-3) and you can expect there to be some missing ids (as would be normal if you have an identity field).

Try this:

CREATE TABLE #temp (id INT, value INT)

INSERT INTO #temp 
SELECT 1,2
UNION ALL
SELECT 2,8
UNION ALL
SELECT 3,8
UNION ALL
SELECT 5,19

SELECT id, value, ROW_NUMBER() OVER (ORDER BY id) AS rownumber 
INTO #temp2
FROM #temp

SELECT * FROM #temp2

SELECT CAST(b.id AS VARCHAR(10)) + '-' + CAST(a.id AS VARCHAR(10)) AS col1,
    a.value + b.value as Col2 
FROM #temp2 a
JOIN #temp2 b 
    ON a.rownumber = b.rownumber+1
WHERE ABS(a.rownumber)%2 = 0 

Assuming that col1 is integer

SELECT CAST(a.col1 as VARCHAR(10))+ '-' + CAST(b.col1 as VARCHAR(10)), COALESCE(a.col2,0)+COALESCE(b.col2,0)
FROM table a
JOIN table b a.col1 = b.col1 + 1

you can test following query also...

I have oracle in my machine that's why I can run and say only oracle queries..

please check whether this will work on sql server also or not and tell me about ...

select * from 
     (Select lag (col1) over (order by col1)|| '-' || col1  as col1
      col2 + lag (col2) over (order by col1) as Col2 
     From mytable
     )
where col2 is not null;

in oracle lag () function used to fatch last row values.. and if it is first row then this function will give null values.. so that by appling addition on null values you will get null only

by this concept we will get desired output...