Display multiple rows of one table into one row without using PIVOT

Question

I'm currently trying to form a SELECT query to from a table without using PIVOT.

Examples of data and expected result are shown below:

DB Data

| Id | TYPE | Amount |
| 1 | Deposit | 50 |
| 2 | Withdraw | 10 |
| 3 | Withdraw | 30 |

Expected Result

| Row | TYPE | Amount | TYPE | Amount | 
| 1 | Deposit | 50 | Withdraw | 10 |
| 2 | - | 0 | Withdraw | 30 |

The deposits and withdrawals are not related to each other, just displaying in ascending order of id. The expected end result is two row of all deposits while another two row with withdrawals. The purpose of doing this is to maximize spaces.

Thought of using left join but there's no unique id that can let me use GROUP BY

Thanks in advance for helping.

Answer 1

declare @t table (Id int, [TYPE] varchar(100), Amount int);
insert into @t values
( 1, 'Deposit', 50),
( 2, 'Withdraw', 10),
( 3, 'Withdraw', 30);
with cte as
(
select *, ROW_NUMBER() over(partition by [TYPE] order by id) n
from @t t
),
nums as
(
select distinct n
from cte
)
select n.n as row, c1.TYPE, c1.Amount, c2.TYPE, c2.Amount
from nums n
 left join cte c1 
 on n.n = c1.n and c1.TYPE = 'Deposit'
 left join cte c2 
 on n.n = c2.n and c2.TYPE = 'Withdraw';

• JamesYTL: ROW_NUMBER() over(partition by [TYPE] order by id) n is exactly what I'm lacking in my LEFT JOIN query. Thanks!