Display positive and negative values in different column

Question

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 F1 -------- 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?

Sachin Singh · Answer

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 ON (T1.C1 = T2.C2);

Puneet Kankar · Answer

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 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 Kumar · Answer

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 Full outer join ( Select * ,Row_Number() over( order by col1)Rownum from T1 where col1 >=0)Pos on Neg.Rownum=Pos.Rownum

MAHAMDSALAM MULLA · Answer

select (case when F1>0 then F1 end) as pos_num, (case when F1<0 then F1 end) neg_num from T1;

Muhammad Imran Ansari · Answer

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] FROM Table1

Mubarak Hashmi · Answer

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 full outer join (select *,ROW_NUMBER()over( order by id) rownum from sample where id>=0)Pos On neg.rownum=pos.rownum

Sujit Sahoo · Answer

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

Jignesh Kumar · Answer

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