SQL Server Performance Forum – Threads Archive

# Query Manipulation

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

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.
—What’s the defitinion of the Fibonacci series – each number in the series is the sum of all previous 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)

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:

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,*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.
—What’s the defitinion of the Fibonacci series – each number in the series is the sum of all previous 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)

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

quote:

So which one do you want to use now?
Madhivanan
Failing to plan is Planning to fail
*Originally posted by Jeya prabhu*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

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)

]]>