需要将输出作为一个列的正数和负数从一个表中获取到不同的列中

I need a solution for the scenario below:

我需要以下方案的解决方案:

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

I want the OUTPUT as:

我希望输出为:

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

3 个解决方案

#1

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))

SQL Fiddle Demo

SQL小提琴演示

#2

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 demo

SQLFiddle演示

If POSITIVE values always exist for each NEGATIVE in the table then use LEFT JOIN instead of FULL:

如果表中每个负数都存在正值，则使用左连接而不是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

#3

This correlated subquery does what you want using ABS:

这个相关子查询使用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

Sql-Fiddle

I find it easier to read as a FULL OUTER self JOIN.

我发现作为一个完整的外部自我连接更容易阅读。

#1