Click here to Skip to main content
14,733,542 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table of one column as Shown below

##Temp
-----
ID
-------
1
-2
2
-3
-4
6
8

I want result should be in two columns positive number as one column and negative number as another column.


output should be
Positive Negative
---------------
1 -2
2 -3
6 -4
8 NULL


Please help me

Thanks
Posted
Comments
jaket-cp 10-Sep-15 11:06am
   
does it matter what order the positive and negative are in?

If you want the order as in you question, you could do something like this:
with numTab as (
--setup dummy data
	select
		1 ID
	union all select -2
	union all select 2
	union all select -3
	union all select -4
	union all select 6
	union all select 8
)
select
	posNum.ID Positive,
	negNum.ID Negative
from 
	(select 
		row_number() over(order by ID) rowid,
		ID
	from numTab where ID > = 0) posNum
--use full join instead of left or right join 
full join 
	(select 
		row_number() over(order by ID desc) rowid,
		ID
	from numTab where ID < 0) negNum
	on posNum.rowid = negNum.rowid
;

That should do what you required.
   
v3
Comments
Andy Lanng 10-Sep-15 11:17am
   
Nice. 5'ed
jaket-cp 10-Sep-15 11:18am
   
:)
Maciej Los 10-Sep-15 15:26pm
   
5ed!
jaket-cp 11-Sep-15 4:10am
   
:) thanks
Here's one way: Add a couple of temporary tables with identity values, one for +ve values and one for -ve. Insert the values from each in ascending and descending order respectively (if you want the lower -ve figures to go with the higher +ve ones as per your example). Then do a query joining the two tables on the identity column.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900