Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- | Id | TYPE | Amount |
- | 1 | Deposit | 50 |
- | 2 | Withdraw | 10 |
- | 3 | Withdraw | 30 |
- | Row | TYPE | Amount | TYPE | Amount |
- | 1 | Deposit | 50 | Withdraw | 10 |
- | 2 | - | 0 | Withdraw | 30 |
- 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';
Add Comment
Please, Sign In to add comment