Positive and Negative number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Positive and Negative number

Author

Topic

rahul8346
Starting Member

21 Posts

Posted - 2006-05-04 : 06:28:18

hi guys,
I am having a table tblNumber(id int,Number int). I wish to get the positive and negative number from the column "Number" to be displayed as two different column as Positive and Negative number.
I am trying this code

1) It is giving null values in some cell
SELECT CASE
WHEN number > 0 THEN num
END as positive,
CASE
WHEN number < 0 THEN num
END as neagtive
FROM tblNumber

2) It is giving duplicate data

select a.num as [positive],
b.num as [negative]
from tblNumber a,tblNumber b
where a.num >=0 and b.num<0

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-04 : 07:14:13

[code]
select (select number from tblnumber where number>0) as positive,
(select number from tblnumber where number<0) as negative
[/code]

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

Kristen
Test

22859 Posts

Posted - 2006-05-04 : 07:16:29

1)

SELECT CASE
WHEN number > 0 THEN num
ELSE ... some value here ...
END as positive,
CASE
WHEN number < 0 THEN num
ELSE ... some value here ...
END as neagtive
FROM tblNumber

2) You have no JOIN between A and B, so every row in A will be matched with every row in B !!

This might do the trick

select a.num as [positive],
NULL as [negative]
from tblNumber a
where a.num >=0
UNION ALL
select NULL as [positive],
b.num as [negative]
from tblNumber b
where b.num<0

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-04 : 07:58:29

quote:
Originally posted by spirit1

select (select number from tblnumber where number>0) as positive,
(select number from tblnumber where number<0) as negative

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

That will lead to error if the subquery returns more than one value

Madhivanan

Failing to plan is Planning to fail

rahul8346
Starting Member

21 Posts

Posted - 2006-05-04 : 08:56:31

hi txs for the reply,
but for the first stated query it gives error

1)select (select num from tblnumber where num>0) as positive,(select num from tblnumber where num<0) as negative

Error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

2) This one works fine but the same problem remains, it fills Null values in the Cells.

select a.num as [positive],
NULL as [negative]
from tblNumber a
where a.num >=0
UNION ALL
select NULL as [positive],
b.num as [negative]
from tblNumber b
where b.num<0

It should give output as

positive | Negative
---------------------
12 -9
45 -5

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-04 : 08:59:29

Can you post some sample data and the result you want?
When it is NULL do you want to show them as 0?

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-04 : 09:01:42

try to read coalesce in Books OnLine to avoid null values and incorporate that in your query... it's just a presentation problem

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-05-04 : 09:26:30

"This one works fine but the same problem remains, it fills Null values in the Cells"

Then use my other one with the ELSE ... some value here ... and put an appropriate value in for rows that do not have a matching value. Would zero do?

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 09:47:23

quote:
I am having a table tblNumber(id int,Number int)
It should give output as

positive | Negative
---------------------
12 -9
45 -5

I think this is one of those strange requirement that just want 2 sets of unrelated value to appear side by side.
Poster's data is probably

id Number
1  12
2  -9
3  45
4  -5

And Poster wanted 12 and -9 on the same row. I have seen couple of questions like this lately.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-04 : 10:00:18

[code]
declare @numbers table (number int )
insert @numbers
select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9 union all select -1 union all
select -2 union all select -3 union all select -4 union all select -5 union all select -6

select identity(int,1,1) as id, number
into #tempPos
from @numbers
where number > 0

select identity(int,1,1) as id, number
into #tempNeg
from @numbers
where number < 0

select t1.number as positve, t2.number as negative
from #tempPos t1
left join #tempNeg t2 on t1.id = t2.id

drop table #tempPos, #tempNeg
[/code]

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

rahul8346
Starting Member

21 Posts

Posted - 2006-05-05 : 06:07:47

Actually, KHtan have understood me right, and i wanted to perform this task in a single query without using any temporary table.

AS Khtan have mentioned
-------------------------------------------------------------------------
I think this is one of those strange requirement that just want 2 sets of unrelated value to appear side by side.
Poster's data is probably

id Number
1 12
2 -9
3 45
4 -5

And Poster wanted 12 and -9 on the same row. I have seen couple of questions like this lately.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-05 : 06:12:14

nope ... not possible in a single query...

you could use a function.

and how would you tell it which number comes next to which?

and this is probably a front end issue. return 2 recordsets one with positive and one with negative
numbers and show them in a way you want.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-05 : 08:29:53

I think this can be done in a single query, but the poster will need to tell us how to associate two different records so they appear on the same output row.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-05 : 08:40:00

ok if you have an association i agree... but without one... i don't think there is a way, is there?
because the whole point of the temp tables and identites is to enforce an association.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-05-05 : 08:59:30

I think we're going to find it's something like this (SQL Server 2005):


SELECT N.number, P.number
FROM (
    SELECT ROWNUMBER() OVER (ORDER BY id) AS row, number
    FROM tblNumber
    WHERE number < 0
  ) AS N
FULL OUTER JOIN (
    SELECT ROWNUMBER() OVER (ORDER BY id) AS row, number
    FROM tblNumber
    WHERE number > 0
  ) AS P
  ON N.row = P.row
ORDER BY COALESCE(N.row, P.row)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 05:06:01

How about this??



Declare @Tbl Table 
(
ID char(1), 
Num int 
)
Insert @Tbl 
Select 'a', 12 Union All 
Select 'b', -9 Union All 
Select 'c', 45 Union All 
Select 'd', -5 


Select A.Num,b.Num  From 
(Select (Select Count(1) From @Tbl a Where a.[Num] <= b.[Num] And a.Num<0 ) as RowNum ,b.Num From @Tbl b Where b.Num<0 ) as A 
Inner Join 
(Select (Select Count(1) From @Tbl a Where a.[Num] <= b.[Num] And a.Num>0) as RowNum ,b.Num From @Tbl b Where b.Num>0 ) as B 
On A.RowNum = b.RowNum

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rahul8346
Starting Member

21 Posts

Posted - 2006-05-08 : 08:57:25

Thank You...Guys.
You all are certainly Awesome.Thanks for the Help.
I am sure i will never get scared of SQL until this Team Exist.
Txs chiragkhabaria, I just works Fine...with the only exception that it will not work for Duplicates records.
Txs A Lot

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-08 : 13:35:16

yeah it will not work for the duplicate records... there has to some some uniqure idenity in the table..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 01:31:25

quote:

Originally posted by chiragkhabaria

How about this??



Declare @Tbl Table 
(
ID char(1), 
Num int 
)
Insert @Tbl 
Select 'a', 12 Union All 
Select 'b', -9 Union All 
Select 'c', 45 Union All 
Select 'd', -5 


Select A.Num,b.Num  From 
(Select (Select Count(1) From @Tbl a Where a.[Num] <= b.[Num] And a.Num<0 ) as RowNum ,b.Num From @Tbl b Where b.Num<0 ) as A 
Inner Join 
(Select (Select Count(1) From @Tbl a Where a.[Num] <= b.[Num] And a.Num>0) as RowNum ,b.Num From @Tbl b Where b.Num>0 ) as B 
On A.RowNum = b.RowNum

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Not advisible if the table has thousands of records

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources