CODEWITHSUNDEEP
sqloracle-databasemerge
Piyush
Piyush

Reputation: 13

How to merge rows in select query result set

My result of is like this 

date      acc     cr      date       acc     dr
---------------------------------------------------
null      null    0       13/3/12    A       1300
null      null    0       13/3/12    c       1200 
null      null    0       13/3/12    D       1100
13/3/12   A       1000    null       null    0
18/3/12   E       2000    null       null    0
19/3/12   F       3000    null       null    0
31/3/12   G       3000    null       null    0

this result i got from following query bu joining 2 tables to get cash book

select(case when mli.voucher_type = 1 THEN tav.voucher_date end)pdate,
(case when mli.voucher_type = 1 THEN mli.description end) acc,
(case when tav.voucher_type_id = 1 then sum(tvl.amount) else 0 end) cr,
(case when mli.voucher_type = 2 THEN tav.voucher_dateend) rdate,
(case when mli.voucher_type = 2 THEN mli.description end) acc,
(case when tav.voucher_type_id = 2 then sum(tvl.amount) else 0 end) dr
from t_acc_voucher tav
join t_voucher_ledger tvl on tvl.voucher_id = tav.voucher_id
join m_ledger_index mli on mli.ledger_index_id = tvl.ledger_index_id
group by mli.description, mli.voucher_type, tav.voucher_type_id,tav.voucher_date

I want result like this

date      acc     cr      date       acc     dr
---------------------------------------------------
13/3/12   A       1000    13/3/12    A       1300
18/3/12   E       2000    13/3/12    c       1200
19/3/12   F       3000    13/3/12    D       1100
31/3/12   G       3000    null       null    0

can any body help me.or give some suggestion is it write way to get it or i can try with 2 diffrent query. thanks in advance

Upvotes: 0

Views: 512

Answers (2)

San
San

Reputation: 4538

Break your query into two separate queries, one for credit and another for debit and do a full outer join based on descending date, you can order by any column in fact. From the original query, I assumed that VOUCHER_TYPE = 1 is credits and VOUCHER_TYPE = 2 is debit.

Try this (not tested)

with CREDITS as (select TAV.VOUCHER_DATE PDATE,
                        MLI.DESCRIPTION ACC,
                        (case when TAV.VOUCHER_TYPE_ID = 1 then SUM(TVL.AMOUNT) else 0 end) CR
                   from t_acc_voucher tav
                   join t_voucher_ledger tvl 
                        on tvl.voucher_id = tav.voucher_id
                   join M_LEDGER_INDEX MLI 
                        on MLI.LEDGER_INDEX_ID = TVL.LEDGER_INDEX_ID
                  where MLI.VOUCHER_TYPE = 1
                  group by MLI.DESCRIPTION, 
                           MLI.VOUCHER_TYPE, 
                           TAV.VOUCHER_TYPE_ID,
                           TAV.VOUCHER_DATE),
      debits as (select TAV.VOUCHER_DATE RDATE,
                        MLI.DESCRIPTION ACC,
                        (case when TAV.VOUCHER_TYPE_ID = 2 then SUM(TVL.AMOUNT) else 0 end) DR
                   from T_ACC_VOUCHER TAV
                  where mli.voucher_type = 2
                   join t_voucher_ledger tvl 
                        on tvl.voucher_id = tav.voucher_id
                   join M_LEDGER_INDEX MLI 
                        on MLI.LEDGER_INDEX_ID = TVL.LEDGER_INDEX_ID
                  group by MLI.DESCRIPTION, 
                           MLI.VOUCHER_TYPE, 
                           TAV.VOUCHER_TYPE_ID,
                           TAV.VOUCHER_DATE  )
select T1.PDATE, T1.ACC, T1.CR, T2.RDATE, T2.ACC, T2.DR
  from (select a.*, row_number() over (order by a.PDATE) RN
          from credits a) T1
  full outer join 
        select b.*, row_number() over (order by b.RDATE) RN
          from debits b) T2
       on (t1.rn = t2.rn);

Upvotes: 1

StanislavL
StanislavL

Reputation: 57421

select t1.date1, t1.acc1, t1.cr, t2.date2, t2.acc2, t2.dr
from (the table or query) t1 
    join (the table or query) t2 on t1.acc1=t2.acc2
where t1.acc1 is not null

we join the same query (or table) twice joinint to itself by acc.

Upvotes: 0

Related Questions