query one column into 2 - 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)

query one column into 2

Author

Topic

Teachme
Starting Member

45 Posts

Posted - 2006-09-28 : 11:43:21

if i have a table with one cloumn of decimal datatype and i wana query values >0 into one column and values<0 into another. How do i do that. i wud really appreciate any kind of help. Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 11:45:45

Something like this?

Select
  (Case when Col1 > 0 then Col1 else 0 end) as GreaterThanZero,
  (Case when Col1 < 0 then Col1 else 0 end) as LessThanZero
From Tbl

Harsh Athalye
India.
"Nothing is Impossible"

Teachme
Starting Member

45 Posts

Posted - 2006-09-28 : 11:54:47

thanks for ur help...yeah exactly tats wat i was lookin for but how do i get rid off the fields with o's in them i just want numbers greater or less than zero. thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 12:17:15

You can't avoid that...since there may be cases where col1 has non-zero value but col2 doesn't or vice versa, like this:

greater      less
-------------------
1            0
5            0
0            -1
0            -55
4            -4

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 12:19:19

[code]Select
(Case when Col1 > 0 then Col1 end) as GreaterThanZero,
(Case when Col1 < 0 then Col1 end) as LessThanZero
From Tbl
[/code]

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 12:25:01

quote:
Originally posted by madhivanan

Select
(Case when Col1 > 0 then Col1 end) as GreaterThanZero,
(Case when Col1 < 0 then Col1 end) as LessThanZero
From Tbl

Madhivanan

Failing to plan is Planning to fail

Madhivanan,

In this case, where the CASE condition fails, NULLs will be returned !

Harsh Athalye
India.
"Nothing is Impossible"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 13:02:02

What's wrong with returning nulls? It may be the best solution here.

One thing I would want to know is what he wants to do with rows where the value = 0?

CODO ERGO SUM

Teachme
Starting Member

45 Posts

Posted - 2006-09-28 : 14:31:33

isn't there any way that i can only get the values..no zeros or nulls..caz i have to join the result with other tables and do a report on it crystal. thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 14:54:02

quote:
Originally posted by Teachme

isn't there any way that i can only get the values..no zeros or nulls..caz i have to join the result with other tables and do a report on it crystal. thanks

You really need to show a sample of the result set you are after.

If you want two columns, one with > 0 and one with < 0, what would you put in the other columnm, since no number can fit both conditions?

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 21:12:23

You should post some sample data and the result you want

>>isn't there any way that i can only get the values..no zeros or nulls

Simply do

Select col from talbe where col is not NULL and col<>0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources