SQL select statement using same column twice

Question

I have a simple select statement from one table that lists the same numeric column (TRANAMT) twice. If the number is positive (>0) I want it to show in the first reference of TRANAMT. If the number is negative (<0) I want it to should in the next reference of TRANAMT. Ideally if the number is positive it will show on the first reference and the second will display 0 or null. If negative the first reference shows 0 or null and the second reference shows the negative number.

select FNAME, LNAME, TRANAMT, TRANAMT
from CMLEDG

Please let me know if this is possible.
Thanks Ken

Answer 1

Try something below. See if it works

SELECT FNAME
    ,LNAME
    ,CASE 
        WHEN TRANAMT > 0
            THEN TRANAMT
        ELSE NULL
        END TRANAMT_Positive
    ,CASE 
        WHEN TRANAMT < 0
            THEN TRANAMT
        ELSE NULL
        END TRANAMT_Negative
    ,TRANAMT
FROM CMLEDG

---

If the response helped, do "Accept Answer" and upvote it - Vaibhav

Answer 2

Hi,

Q&A currently supports the products listed over here https://learn.microsoft.com/en-us/answers/products (more to be added later on).

You can reach the experts in the dedicated SQL Server forum over here:
https://social.technet.microsoft.com/Forums/en-US/home?category=sqlserver

(Please don't forget to accept helpful replies as answer)

Best regards,
Leon