January 20, 2020 at 5:18 pm
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
January 20, 2020 at 6:18 pm
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
January 21, 2020 at 1:08 pm
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