Help with SQL query

  • Hi Guys,

    Here is my "Sample" source data.

    ID,DKey,SKey,InvType ,PID,PAmount,CAmount,AAmount

    1 ,123 ,456 ,Write-Off,789,$505 ,$767 ,$262

    2 ,958 ,258 ,Invoice , , ,$621 ,

    Here what I want or the end result

    ID,DKey,SKey,InvType ,PID,PAmount,CAmount,AAmount

    1 ,123 ,456 ,Invoice , , ,$767 ,

    1 ,123 ,456 ,Write-Off,789,$505 , ,$262

    2 ,958 ,258 ,Invoice , , ,$621 ,

    Note:- The logic I want from source data, IF PID is not null then create a another row with same informations (ID,DKey,SKey) and give the InvType = Invoice

    I know I can use "Union All". Is it better way to do this ? Please advice. Below is sample script.

    DECLARE @TempTable TABLE
    (IDint,
    DKeyint,
    SKeyint,
    InvTypevarchar(25),
    PIDint,
    PAmountmoney,
    CAmountmoney,
    AAmountmoney
    )

    Insert into @TempTable
    Values
    (1,123,456,'Write-Off',789,505,767,262),
    (2,958,258,'Invoice',0,0,621,0)

    Select * from @TempTable
  • union all is what you need ... but not necessarily the way you were thinking about it

    declare @Temptable table
    ( ID int
    , DKey int
    , SKey int
    , InvType varchar(25)
    , PID int
    , PAmount money
    , CAmount money
    , AAmount money
    )

    insert into @Temptable
    values (1, 123, 456, 'Write-Off', 789, 505, 767, 262)
    , (2, 958, 258, 'Invoice', null, null, 621, null)

    select vals.*
    from @Temptable t
    outer apply (select t.ID
    , t.DKey
    , t.SKey
    , t.InvType
    , t.PID
    , t.PAmount
    , case
    when t.PID is not null
    then null
    else t.camount
    end as camount
    , t.AAmount
    union all
    select t.ID
    , t.DKey
    , t.SKey
    , 'Invoice' as InvType
    , null as PID
    , null as PAmount
    , t.camount
    , null as AAmount
    where t.PID is not null
    ) vals
  • Yes, union all works well.  Or you could insert into @Temptable twice.  It's not clear whether the information in the inserted 'Invoice' should contain all of "same informations (ID,DKey,SKey)..." because the output shown adds nulls to certain columns.  Here's both ways:

    /* match description */
    select * from @TempTable
    union all
    select
    ID, DKey, SKey, 'Invoice' as InvType, PID, PAmount, CAmount, AAmount
    from
    @TempTable
    where
    PID is not null;

    /* match output shown */
    select * from @TempTable
    union all
    select
    ID, DKey, SKey, 'Invoice' as InvType, null PID, null PAmount, CAmount, null AAmount
    from
    @TempTable
    where
    PID is not null;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

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

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