嗨Spunny，

请查看：

 DECLARE @tmp TABLE（
 ID int，
 TrnID bigint，
 MemberName varchar（250），
 AccountID int，
 TrnType varchar（100），
 SubTrnType varchar（100），
金额十进制（30,10），
 InternalAccountID varchar（50），
 TrnCategory varchar（50），
 CreationDate DateTime 
）; 

将INTO @tmp（ID，TrnID，成员名称，帐户ID，TrnType，SubTrnType，金额，InternalAccountID，TrnCategory，CreationDate）
值（1859，20190520000195， 'TEST1'，1796年， 'CashTransfer'， '取款'， 821.90，'BK-4001-1796'，'内部'，'2019-05-20 00：00：00.000'）
，（1860,20190520000195，'TEST1'，665，'CashTransfer'，'存款' ，821.90，'BK-4001 -665'，'内部'，'2019-05-20 00：00：00.000'）
，（2491,20190520001029，'TEST2'，200，'CashTransfer'，'Withdraw'，5000.75，'BK- 2001-200'，'内部'，'2019-05-20 00：00：00.000'）
，（2491,20190520001029，'TEST2'，844，'CashTransfer'，'存款'，5000.75，'BK -0001-844'，'INTERNAL'，'2019-05-20 00：00：00.000'）; 

 SELECT * FROM @tmp; 

; WITH AS 
 RS（
选择a.ID，a.TrnID，a.AccountID AS DEBIT_ACT，a.Amount，a.InternalAccountID AS DEBIT_INTERNALACCOUNTID 
， a.CreationDate 
，b.AccountID AS CREDIT_ACT，b.InternalAccountID AS CREDIT_INTERNALACCOUNTID 
，rn = ROW_NUMBER（）OVER（a.TrnID ORDER by a.SubTrnType DESC）
 FROM @tmp AS一个INNER JOIN 
 @tmp为b ON a.TrnID = b.TrnID和左（a.InternalAccountID，7）！= LEFT（b.InternalAccountID，7）
）
 SELECT * 
来自rs 
 WHERE rn = 1; 

结果：

 ID TrnID DEBIT_ACT金额DEBIT_INTERNALACCOUNTID CreationDate CREDIT_ACT CREDIT_INTERNALACCOUNTID 
 2491 20190520001029 200 5000.7500000000 BK-2001-200 2019-05-20 00：00：00.000 844 BK-0001-844 1 

Hi,

This is banking application. I get transactions that are transferred from one account to another account for same member and sometimes I get transactions that are transfers from one account to another account belonging to another member.

I need to get all the transactions that are transfers from one account to another account belonging to another member. Below is the data and tmp table

From InternalAccountID column data (first 7 characters), it can be determined if transfer is from one account to another for same member or different member.

Transfer for Same Member:

               FROM: BK-4001-1796

   TO: BK-4001-665

Transfer for different Member:

               FROM: BK-2001-200

   TO: BK-0001-844

declare @tmp TABLE

(

       ID int,

       TrnID bigint,

       MemberName varchar(250),

       AccountID int,

       TrnType varchar(100),

       SubTrnType varchar(100),

       Amount decimal(30,10),

       InternalAccountID varchar(50),

       TrnCategory varchar(50),

       CreationDate DateTime

)

INSERT INTO @tmp (ID, TrnID, MemberName, AccountID, TrnType, SubTrnType, Amount, InternalAccountID, TrnCategory, CreationDate)

SELECT 1859,  20190520000195, 'TEST1', 1796, 'CashTransfer', 'Withdraw', 821.90, 'BK-4001-1796', 'INTERNAL', '2019-05-20 00:00:00.000'

UNION

SELECT 1860,  20190520000195, 'TEST1', 665, 'CashTransfer', 'Deposit', 821.90, 'BK-4001-665', 'INTERNAL', '2019-05-20 00:00:00.000'

UNION

SELECT 2491,  20190520001029, 'TEST2', 200, 'CashTransfer', 'Withdraw', 5000.75, 'BK-2001-200', 'INTERNAL', '2019-05-20 00:00:00.000'

UNION

SELECT 2491,  20190520001029, 'TEST2', 844, 'CashTransfer', 'Deposit', 5000.75, 'BK-0001-844', 'INTERNAL', '2019-05-20 00:00:00.000'

select * from @tmp

The result I should get is

 Where first 8 characters of InternalAccountID is different for same TrnID ( like BK-2001 is different from BK-0001 for TrnID 20190520001029).

The result set should return both transactions of 20190520001029, but final transaction should be made up of 2 transactions into one like this:

TrnID  DEBIT_ACT     CREDIT_ACT    AMOUNT DEBIT_INTERNALACCOUNTID       CREDIT_INTERNALACCOUNTID, CREATIONDATE

2491   200            844           500.75    BK-2001200                 BK-0001-844        

How can I do.

Thank You            

Hi Spunny,

Please check it out:

DECLARE @tmp TABLE(
       ID int,
       TrnID bigint,
       MemberName varchar(250),
       AccountID int,
       TrnType varchar(100),
       SubTrnType varchar(100),
       Amount decimal(30,10),
       InternalAccountID varchar(50),
       TrnCategory varchar(50),
       CreationDate DateTime
);

INSERT INTO @tmp (ID, TrnID, MemberName, AccountID, TrnType, SubTrnType, Amount, InternalAccountID, TrnCategory, CreationDate)
VALUES( 1859,  20190520000195, 'TEST1', 1796, 'CashTransfer', 'Withdraw', 821.90, 'BK-4001-1796', 'INTERNAL', '2019-05-20 00:00:00.000')
	, (1860,  20190520000195, 'TEST1', 665, 'CashTransfer', 'Deposit', 821.90, 'BK-4001-665', 'INTERNAL', '2019-05-20 00:00:00.000')
	, (2491,  20190520001029, 'TEST2', 200, 'CashTransfer', 'Withdraw', 5000.75, 'BK-2001-200', 'INTERNAL', '2019-05-20 00:00:00.000')
	, (2491,  20190520001029, 'TEST2', 844, 'CashTransfer', 'Deposit', 5000.75, 'BK-0001-844', 'INTERNAL', '2019-05-20 00:00:00.000');

SELECT * FROM @tmp;

;WITH rs AS
(
SELECT a.ID, a.TrnID, a.AccountID AS DEBIT_ACT, a.Amount, a.InternalAccountID AS DEBIT_INTERNALACCOUNTID
	, a.CreationDate
	, b.AccountID AS CREDIT_ACT, b.InternalAccountID AS CREDIT_INTERNALACCOUNTID
	, rn = ROW_NUMBER() OVER(PARTITION BY a.TrnID ORDER BY a.SubTrnType DESC)
FROM @tmp AS a INNER JOIN
	@tmp AS b ON a.TrnID = b.TrnID AND LEFT(a.InternalAccountID,7) != LEFT(b.InternalAccountID,7)
)
SELECT *
FROM rs
WHERE rn = 1;

Outcome:

ID	TrnID	DEBIT_ACT	Amount	DEBIT_INTERNALACCOUNTID	CreationDate	CREDIT_ACT	CREDIT_INTERNALACCOUNTID	rn
2491	20190520001029	200	5000.7500000000	BK-2001-200	2019-05-20 00:00:00.000	844	BK-0001-844	1