how do we use SQL server Self join for the following situation?

Question

I have a table Transactions, having following columns:

txn_id,
Account_id,
Amount,
txn_id,
txn_type_id

txn_type_id is the column which shows whether the transaction was CDT or DBT, using self join I want to present the records to show the CDT and DBT transactions separately

The code I used is :

select 
    t1.txn_id as DBTAccId, 
    t1.Account_id as DBTAccountid,
    t1.Amount as DBTAmount, 
    t2.txn_id as CDTId, 
    t2.Account_id as CDTAid,
    t2.Amount as CDTAmount,
    t1.txn_type_id
from Transactions t1 , Transactions t2
where t1.txn_id <> t2.txn_id
and t1.txn_type_id = t2.txn_type_id;

But it prints all records for a single txn_id, just like

tx_id    txn_type_id
1           CBT
1           CBT
1           CBT
1           CBT
1           CDT
1           CDT
2           CBT
2           CBT
2           CBT
2           CBT
2           CDT
2           CDT

and i want it to print like:

1           CBT
2           CBT
3           CDT
4           CDT

Answer 1

You don't want a self-join; you want order by:

select t.*
from Transactions t
order by t.txn_type_id, t.txn_id;

Answer 2

If order by isn't the fix, I get the impression that you might be looking for results in two columns:

with dbt as (
    select *, row_number() over (partition by Account_id order by txn_id) as rn
    from Transactions where tx_type_id = 'DBT'
), cbt as (
    select *, row_number() over (partition by Account_id order by txn_id) as rn
    from Transactions where tx_type_id = 'CBT'
)
select *
from dbt d full outer join cbt c
    on c.Account_id = d.Account_id and c.rn = d.rn;