SQL Server Performance Forum – Threads Archive

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.

---

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)

---

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."

---

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."

---

If Adriaan’s code gives you the correct result then , you can alos use this
Select t1.value+t2.value from tbl t1
inner join tbl t2 on t1.row_number=t2.row_number-1
Madhivanan Failing to plan is Planning to fail

---

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.

---

Well. In that case,
Declare @rows int
Select @rows=max(row_number) from tbl
select @rows If @rows%2=0
Select t1.value+t2.value from tbl t1
inner join tbl t2 on t1.row_number=t2.row_number-1 else
Select t1.value+t2.value from tbl t1
inner join tbl t2 on t1.row_number=t2.row_number-1
and t2.row_number<@rows
Madhivanan Failing to plan is Planning to fail

---

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.

---

So which one do you want to use now? Madhivanan Failing to plan is Planning to fail

---

"…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

---

What do you ultimately want to do with these statistical numbers? —
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)

---

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."

---

Well. What is your answer for Frank’s question? Madhivanan Failing to plan is Planning to fail

---

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

---

I suspected something like that. As long as you need simple things like variance it should be fine to do this with SQL Server. However, more sophisticated measures aren’t really fun on larger tables and you might in the end be better off just returning the resultset to the client and do the calculations there. SQL isn’t really made for complex calculations. Btw, Steve Kass has several interesting statistical scripts on his homepage:http://www.users.drew.edu/skass/sql/ —
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)

---

]]>