Display positive and negative values in different column

Sujit Sahoo

7y8k

Here, I have one Table T1.

In that table, I have one column F1 having some positive and negative values.

I want to display those values in two different columns.

select * from T1

--------

1000

-2000

500

-1000

2000

-300

I want to display like this

Pos Neg

------- ------

1000 -2000

500 -1000

2000 -300

How to do this any suggestion?

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar4y ago
Hi,

Please use this one it will help you,
select Neg.col1 as Negative ,Pos.col1 as Positive from
(Select * ,Row_Number() over(order by col1) Rownum from T1
where col1 <0)Neg
Full outer join
(Select * ,Row_Number() over(order by col1)Rownum from T1
where col1 >=0)Pos
on Neg.Rownum=Pos.Rownum
MAHAMDSALAM MULLA4y ago
select (case when F1>0 then F1 end) as pos_num, (case when F1<0 then F1 end) neg_num from T1;
Sachin Singh4y ago
This should work
SELECT T1.POSITIVE_VALUE, T2.negetive_value
FROM (select F1 positive_value, ROWNUM C1 FROM T1 WHERE F1>= 0) T1
FULL OUTER JOIN (SELECT F1 negetive_value, ROWNUM C2 FROM T1 WHERE COL < 0) T2
ON (T1.C1 = T2.C2);
Muhammad Imran Ansari4y ago
You can also do this through CASE statement:
CREATE TABLE Table1 (val INT)
INSERT INTO Table1 VALUES(1000), (-2000), (5000), (-1000), (2000), (-3000)
SELECT (CASE WHEN [val] > 0 THEN [value] ELSE 0 END) AS Positive,
(CASE WHEN [val] < 0 THEN [value] ELSE 0 END) AS Negative
FROM Table1
Mubarak Hashmi4y ago
Create table sample

(

id int

)

insert into sample values(1000),(-1000),(500),(-2000),(2000),(-300)

select Neg.id as Negetive,Pos.id as Positive

from

(

select *,ROW_NUMBER()over( order by id) rownum from sample

where id<0)Neg

full outer join

(select *,ROW_NUMBER()over( order by id) rownum from sample

where id>=0)Pos

On neg.rownum=pos.rownum
Sujit Sahoo7y ago
My table values are like this

F1

-----

1000

-2000

500

-1000

2000

-300

I want output like this

Positive Negative

------- ------

1000 -2000

500 -1000

2000 -300

I have executed this query

select t1.F1 as POSITIVE,

B.F1 as NEGATIVE

FROM t1

LEFT JOIN T1 as B on (-T1.F1=B.F1)

WHERE T1.F1>0

ORDER BY T1.F1

Output

----------

POSITIVE NEGATIVE

500 NULL

1000 -1000

2000 -2000

I didn't get my -300 value here at the value of -300 i got the null value.

when both the values are same means my positive and negative value, my output is ok

output

--------

POSITIVE   NEGATIVE

500 -500

1000 -1000

2000 -2000

if I have different positive and negative values then how to get output?

Positive Negative

-------                ------

1000                 -2000

500 -1000

2000 -300
Puneet Kankar7y ago
TRY GIVEN BELOW QUERY
create table T1  (col1 int)

insert into T1  values
(1000), (-2000), (500), (-1000), (2000), (-300)



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


OR

SELECT CASE WHEN LEFT(col1,1)='-' THEN col1 ELSE '' END AS NEGATIVE ,CASE WHEN LEFT(col1,1)<>'-' THEN col1 ELSE '' END AS POSITIVE   FROM T1
or
select t1.Col1 as POSITIVE,
B.Col1 as NEGATIVE
FROM t1
LEFT JOIN T1 as B on (-T1.col1=B.Col1)
WHERE T1.Col1>0
ORDER BY T1.Col1
Jignesh Kumar7y ago
Please try below link,

https://stackoverflow.com/questions/30328324/need-to-get-the-output-as-positive-and-negative-numbers-from-one-column-into-dif