Issue:As an active user of various forums and groups someone asked below question on SQL Groups. I found the question interesting so I am writing a post of it in my website.
Below is the question asked by user?
Hi friends
Column A
2000
-1000
4000
-3000
(Using SQL server)
Q/ I want the negative and positive values in separate column's.
2000
-1000
4000
-3000
(Using SQL server)
Q/ I want the negative and positive values in separate column's.
Note ; without using case and string functions..
Please share the ans asap
Please share the ans asap
Thanks
Solution: Even though there are lots of solutions of this Question,but below answer is simple and it's without using any Case statement.
--Test table code contain Positive and Negative values
create table #temp
(Numbers int)
insert into #temp values(-1),(2),(-3),(4),(4)
--Select * from #temp
--Output query
select Neg.Numbers as Negative ,Pos.Numbers as Positive from
(Select * ,Row_Number() over(order by Numbers) Rownum from #temp
where numbers <0)Neg
Full outer join
(Select * ,Row_Number() over(order by Numbers)Rownum from #temp
where numbers >=0)Pos
on Neg.Rownum=Pos.Rownum
Output
Negative Positive
-3 2
-1 4
NULL 4
Note:If you want sum of all negative and sum of all positive values in different column then you can check my older post Sum of only Positive Values of a column and sum of negative value of a column in SQL-http://www.sqlandssrssolutions.com/2013/11/sum-of-only-positive-values-of-column.html