Query Manipulation

Hello
In my table there is only one column(numeric) containing the value

value
--------------------
1
10
11
50
63
72
0
2
10
5
100

Now I want the out put in the format of.....
1+10
11+50
63+72
0+2
10+5
100--because toatal values are odd so it displays the final one without adding it

ALTER PROCEDURE pr_test1
as
BEGIN

declare @prev numeric
declare @curr numeric
declare @tot numeric
declare @value numeric
declare @i int
declare @cnt int
set @i = 1
declare @test_cursor CURSOR
set @test_cursor = CURSOR FAST_FORWARD
FOR select value from test1
create table #temp_test(value numeric)

set @cnt = (select count(*) from test1)
OPEN @test_cursor
FETCH NEXT FROM @test_cursor INTO @value
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
if @i = 1
begin
set @prev = @value
set @i = 2
end
else
begin
set @curr = @value
set @tot = @prev + @curr
insert into #temp_test values(@tot)
set @i = 1
end
END
FETCH NEXT FROM @test_cursor INTO @value
END
print @cnt % 2 -- if the total number of records are odd, then to print the last record.
if @cnt % 2 <> 0
begin
insert into #temp_test values(@value)
end
select value from #temp_test
END
CLOSE @test_cursor
DEALLOCATE @test_cursor

------------------------
By using this I got the result, but I want the result without using the temp table....by using the select query


S.Jeya Prabhu

"A ship in Harbour is safe,
But that is not what ships are built for."

As long as we don't know the answer to one simple question, there is no way that we can give any structural suggestions. That question is: "Why are the values in this particular order?"

If your source data has row numbers 1, 2, 3 ... 11, then a query like this will give you the desired results:

SELECT
CAST(even_row.value AS VARCHAR(10))
+ CASE WHEN odd_row.value IS NOT NULL THEN '+' + CAST(odd_row.value AS VARCHAR(10)) ELSE '' END
FROM
(SELECT row_number, value FROM table WHERE row_number % 2 = 0) even_row
LEFT JOIN
(SELECT row_number, value FROM table WHERE row_number % 2 = 1) odd_row
ON even_row.row_number = (odd_row.row_number - 1)

If you don't know whether there is a gap in the row numbering, you'll need to copy all data ordered by the row_num into a temp table with an identity column, then use that identity column as the row number for the JOIN.

Of course in SQL 2005 you will have a function that gives you a row number within a rowset, which allows you to avoid the temp table.

[Edit: ON even_row.row_number = (even_row.row_number - 1) was incorrect ...]

first of all I Thank u "Adriaan"

This is for our client need....Instead of adding(which I specified there) we have to find the varience and co-varience, but before calculating that...I just check this by add...but one thing I specfied wrongly...that calculating two, two records...but it should come like fibanacci serious...

What's the defitinion of the Fibonacci series - each number in the series is the sum of all previous numbers?

If you have the order of the rows worked out, you could try a query like this - but beware: it will take exponentially longer as the number of rows increases ...

SELECT t.value + (SELECT SUM(r.value) FROM table r WHERE r.row_number < t.row_number)
FROM table t
ORDER BY t.row_number

You will need an index on row_number!

Because of the exponentially growing execution time, I would definitely check whether a cursor-based procedure is perhaps faster.

Fibanacci series is 1+2,2+3,3+4,4+5,5+6............

S.Jeya Prabhu

"A ship in Harbour is safe,
But that is not what ships are built for."

quote:Originally posted by FrankKalis

quote:
What's the defitinion of the Fibonacci series - each number in the series is the sum of all previous numbers?

Not correct. Each number in the series is "only" the sum of its two predecessors.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

OK then -

SELECT a.value,
(SELECT SUM(x.value)
FROM (SELECT TOP 2 b.value FROM tbl b WHERE b.row_number < a.row_number ORDER BY b.row_number DESC) X)
FROM tbl a
ORDER BY a.row_number

sorry adriaan think confused u...

I want
1+10 ------> as 11
11+50------> as 61
63+72------> as 135
0+2--------> as 2
10+5-------> as 15
100--because total values are odd so it displays the final one without adding it

not in the given format....and also I tried out this below query

SELECT
even_row.value
+ CASE WHEN odd_row.value IS NOT NULL THEN + odd_row.value ELSE '' END
FROM
(SELECT row_number, value FROM test1 WHERE row_number % 2 = 1) odd_row
LEFT JOIN
(SELECT row_number, value FROM test1 WHERE row_number % 2 = 0) even_row
ON even_row.row_number = (odd_row.row_number - 1)
----------------
Result
======
---------------------
NULL
5
9
13
17
21
113
72
12
105

(10 row(s) affected)

First value comes as the null value but the remaining things are fine

You must have missed my last post before yours ... Here's one that starts at the third row, and calculates the sum of the two previous rows - note that you do need a row_number column, or the only alternative is a cursor, which probably takes more time to process:

SELECT
(SELECT SUM(x.value) FROM (SELECT TOP 2 b.value FROM tbl b WHERE b.row_number < a.row_number ORDER BY b.row_number DESC) X)
FROM tbl a
WHERE (SELECT COUNT(*) FROM tbl c WHERE c.row_number < a.row_number) > 1
ORDER BY a.row_number

Thank u very much this is the result I want...

S.Jeya Prabhu

"A ship in Harbour is safe,
But that is not what ships are built for."

Madhivanan, that's too simple, given the problem. You need to be certain of the order of the rows, and you need to skip the first two rows. Also, your query depends on a list of row_number values without any gaps.

Adriaan query is ok but it won't calculate the values for the last record...and madhivanan's is calculating that too....

Once again Thank u All.

"...we have to find the varience and co-varience.."

Use the SQL VAR (sample variance) or VARP (population variance) functions.
I am not sure if you're computing for the coefficient of variation (standard deviation/Population Mean) or the covariance of X and Y = Mean Value of (X - Population Mean X)(Y - Population Mean Y)


May the Almighty God bless us all!
www.empoweredinformation.com

This one

Select t1.value+t2.value from tbl t1
inner join tbl t2 on t1.row_number=t2.row_number-1

So which one do you want to use now?

Madhivanan

my past reply is for Madhivana

S.Jeya Prabhu

"A ship in Harbour is safe,
But that is not what ships are built for."

To Frank
This calculation is to find the BSE (Bombay Stack Exchange)...for that we have to find varience instead of adding and between range of dates...So first I tried will the simple things...Then I will go for that...

Thank U