Clever Geek Handbook

Need to get the result as positive and negative numbers from one column to different columns from one table

I need a solution for the scenario below:

TABLE A:
col1
1
-1
6
-5
2
4
-2
5

I want OUTPUT:

POSITIVE     NEGATIVE
1            -1
2            -2
4            NULL
5            -5
6            NULL
+3


source to share


3 answers


You can try something like:

SELECT t1.col1 AS POSITIVE, t2.col1 AS NEGATIVE
FROM (
   SELECT col1
   from tableA
   WHERE col1 > 0 ) t1
FULL OUTER JOIN (
   SELECT col1
   FROM tableA
   WHERE col1 < 0 ) t2 ON t1.col1 = ABS(t2.col1)
ORDER BY ABS(COALESCE(t1.col1, t2.col1))


Demo SQL Fiddle

+6


source


This correlated subquery does what you want using ABS

:

SELECT POSITIVE = a1.col1,
       NEGATIVE = (SELECT TOP 1 a2.col1
                   FROM TableA a2
                   WHERE a2.col1 < 0 
                   AND   ABS(a2.col1) = a1.col1)
FROM TableA a1
WHERE a1.col1 >= 0 
ORDER BY a1.Col1


Sql-Fiddle

I find it easier to read as FULL OUTER

self JOIN

.

0


source 


select A.Col1 as POSITIVE,
       B.Col1 as NEGATIVE
FROM A
FULL JOIN A as B on (-A.col1=B.Col1)
WHERE A.Col1>0 or B.Col1 < 0
ORDER BY ISNULL(A.Col1,-B.Col1)

SQLFiddle

If POSITIVE values ​​always exist for every NEGATIVE in the table, use LEFT JOIN instead of FULL:

select A.Col1 as POSITIVE,
       B.Col1 as NEGATIVE
FROM A
LEFT JOIN A as B on (-A.col1=B.Col1)
WHERE A.Col1>0 
ORDER BY A.Col1
0


source






More articles:


All Articles