Post reply

Transform two columns data into one column

  • September 8, 2011 at 9:00 am

    #244435

    Hi,

    I need to convert a table which contains two columns of data into a view which holds one column. Please see the example below:

    From Source table

    ACCOUNT | CURRENCY | AMOUNT | LOCAL_CURRENCY | LOCAL_AMOUNT

    ---------------------------------------------------------------------

    1001 | SGD | 10 | USD | 8

    To a working view

    ACCOUNT | CURRENCY | AMOUNT | DATASOURCE

    -----------------------------------------------

    1001 | SGD | 10 | Source

    1001 | USD | 8 | Translated

    Please advise me how can I transform the result to the working view. Any help is greatly appreciated!

  • September 8, 2011 at 9:05 am

    #1378622

    SELECT ACCOUNT, CURRENCY, AMOUNT, 'Source' AS DATASOURCE

    FROM MyTable

    UNION ALL

    SELECT ACCOUNT, LOCAL_CURRENCY, LOCAL_AMOUNT, 'Translated' AS DATASOURCE

    FROM MyTable

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply