locked
selecting from table in sql server database RRS feed

  • Question

  • User836525179 posted

    In the table below, I need all the records to be selected with SQL Query, but in the last column, instead of the numbers 0, 1, and 2, the appropriate text should be replaced by each.

    how should I write the SQL Query?

    For example, select all records and then replace the value of "text 1" instead of the last column of the first record, and replace the "text 2" in column of the second record

    query result must be like : 

    2   5    7    5     ملت    ...............    text 1

    3   5    7    1     ملت    ...............    text 2

    .

    .

    .

    Tuesday, April 7, 2020 4:14 AM

Answers

  • User-18289217 posted
    SELECT 
    Id, UserId, AccId, BardashtAmount, BankName, AccNumber, ShabaNumber, ReqDate,
    CASE AcceptStatus WHEN 0 THEN 'Text1' WHEN 1 THEN 'Text2' ELSE 'Text3' END AS AcceptStatus
    FROM
    MyTable

    HTH

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 7, 2020 9:44 AM

All replies

  • User-18289217 posted
    SELECT 
    Id, UserId, AccId, BardashtAmount, BankName, AccNumber, ShabaNumber, ReqDate,
    CASE AcceptStatus WHEN 0 THEN 'Text1' WHEN 1 THEN 'Text2' ELSE 'Text3' END AS AcceptStatus
    FROM
    MyTable

    HTH

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 7, 2020 9:44 AM
  • User-1853731787 posted

    Create a separate table to map this acceptStatus and its text, so that if tomorrow there is a new entry for this statuses, your query does not change and vice versa.

    Lets assume your data is a table called main table, and the status mapping is in StatusTextMapping table. I am just making names here, you can come with better nomenclature.

    Your StatusTextMapping  table could be like

    StatusId | Status Text
    1            | text 1

    .... other entries 

    It could be like this

    Select main.id, main.userid, main.accid, main.bardhashtAmount, main.bankname, main.accnumber, main.shabanumber, main.reqDate,

    mapping.StatusText

    from MainTable main left join  StatusTextMapping mapping

    on main.acceptStatus = mapping.StatusId

    Tuesday, April 7, 2020 12:25 PM