CODEWITHSUNDEEP
sqlsql-serverjoinmultiple-columns
Yousuf Sultan
Yousuf Sultan

Reputation: 3257

Need to get the output as positive and negative numbers from one column into different columns from a table?

I need a solution for the scenario below:

TableA

col1
2
4
-6
-5
4
-2
5
2

The above tableA is my source, i want the output as:

Positive      Negative
2              -6
4              -5
4              -2
5
2

The output that I am getting is as below:

Positive      Negative
2              NULL
4              NULL
4              NULL
5              NULL
2              NULL
NULL           -6
NULL           -5
NULL           -2

This is the query that I have tried:

SELECT pos.col2 AS 'Positive',neg.col2 AS 'negative'
FROM
(SELECT col2 FROM colum1 WHERE col2 < 0) neg FULL OUTER JOIN
(SELECT col2 FROM colum1 WHERE col2 > 0) pos
ON pos.col2 = neg.col2

Upvotes: 0

Views: 1402

Answers (1)

Devart
Devart

Reputation: 122032

Try this one -

DECLARE @temp TABLE (col INT)

INSERT INTO @temp (col)
VALUES (2), (4), (-6), (-5), (4), (-2), (5), (2)

SELECT Positive, Negative 
FROM (
    SELECT Positive = col, RowNum = ROW_NUMBER() OVER (ORDER BY 1/0) 
    FROM @temp
    WHERE col >= 0
) t
FULL JOIN (
    SELECT Negative = col, RowNum = ROW_NUMBER() OVER (ORDER BY 1/0)  
    FROM @temp
    WHERE col < 0
) t2 on t.RowNum = t2.RowNum

Output -

Positive    Negative
----------- -----------
2           -6
4           -5
4           -2
5           NULL
2           NULL

Upvotes: 1

Related Questions