0

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.

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';
  • ROW_NUMBER() over(partition by [TYPE] order by id) n is exactly what I'm lacking in my LEFT JOIN query. Thanks! – JamesYTL Jan 23 '19 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.