Guest User

Untitled

a guest
Jan 23rd, 2019
74
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.77 KB | None | 0 0
  1. | Id | TYPE | Amount |
  2. | 1 | Deposit | 50 |
  3. | 2 | Withdraw | 10 |
  4. | 3 | Withdraw | 30 |
  5.  
  6. | Row | TYPE | Amount | TYPE | Amount |
  7. | 1 | Deposit | 50 | Withdraw | 10 |
  8. | 2 | - | 0 | Withdraw | 30 |
  9.  
  10. declare @t table (Id int, [TYPE] varchar(100), Amount int);
  11. insert into @t values
  12. ( 1, 'Deposit', 50),
  13. ( 2, 'Withdraw', 10),
  14. ( 3, 'Withdraw', 30);
  15.  
  16. with cte as
  17. (
  18. select *, ROW_NUMBER() over(partition by [TYPE] order by id) n
  19. from @t t
  20. ),
  21.  
  22. nums as
  23. (
  24. select distinct n
  25. from cte
  26. )
  27.  
  28. select n.n as row, c1.TYPE, c1.Amount, c2.TYPE, c2.Amount
  29. from nums n
  30. left join cte c1
  31. on n.n = c1.n and c1.TYPE = 'Deposit'
  32. left join cte c2
  33. on n.n = c2.n and c2.TYPE = 'Withdraw';
Add Comment
Please, Sign In to add comment