Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: How to replace NULL values with 0

    I need to separate the data from one column in to two columns based on the transaction type TRAN_TYPE (C is credit, D is Debit)

    TRAN-TYPE| AMOUNT
    C 20.00
    D 30.00
    C 50.00

    To do that I have this code:

    select
    CASE WHEN TRAN_TYPE = 'D'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as DEBIT,
    CASE WHEN TRAN_TYPE = 'c'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as CREDIT
    FROM HISTORY

    And my output is:

    DEBIT | CREDIT
    ----------------
    NULL | 20.00
    ------|-------
    30.00 | NULL
    ------|-------
    NULL | 50.00
    ------|-------

    but I can’t figure out how to replace the null values with 0

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Coalesce()

    COALESCE (Transact-SQL)

    Code:
    SELECT Coalesce(NULL, NULL, NULL, 'value', 'another value')
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    CAST(ISNULL(CASE WHEN TRAN_TYPE = 'D' THEN amount end, 0) as varchar (30)) as DEBIT,
    CAST(ISNULL(CASE WHEN TRAN_TYPE = 'c' THEN amount end, 0) as varchar (30)) as CREDIT
    Hope this helps.

  4. #4
    Join Date
    Feb 2013
    Posts
    46
    Thanks a lot imex that works!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...wait, why are you casting values back into strings?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2013
    Posts
    46
    Data is exported to excel as a string as far as I can tell, I am new to the project.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh great heavenly days! I realize that you're new to this project, but using SQL Server to pivot data for Excel is silly!

    Just bring the SQL data in raw form onto a blank page then create an Excel Pivot Table to make the data dance any way that suits you! A pivot is a presentation issue, and Excel is far better at presentation issues than SQL Server can or should be!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •