asked on

Query for output in tabular format

Dear Experts

I have a query as below which is on ORACLE 10G

SELECT MEMBERMASTER.MEMBERNAME,
BMBILL.MEMBERID,
BMBILL.BILLDATE,
BMBILLDTL.AMOUNT
FROM BMBILL, BMBILLDTL, MEMBERMASTER
WHERE (BMBILL.INTNO = BMBILLDTL.INTNO)
AND (BMBILL.MEMBERID = MEMBERMASTER.MEMBERID)
AND (BMBILL.MODIFIED = 0)
AND (BMBILL.DELETED = '0')
AND (BMBILLDTL.MODIFIED = 0)
AND (BMBILLDTL.ITEMCODE = 'G081')

I have attached the excel file having two sheets one with present output and one with required output.

It would be of great help, if you can suggest how do i get the required output as mentioned in excel file
format.xls

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jocave

If there are multiple entries for a single month, I'm adding them-- you could use a different aggregate function if you wanted to handle that differently.

SELECT master.membername,
       bill.memberID,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 12 THEN detail.amount ELSE 0 END) ) dec,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 11 THEN detail.amount ELSE 0 END) ) nov,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 10 THEN detail.amount ELSE 0 END) ) oct,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 9 THEN detail.amount ELSE 0 END) ) sep,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 8 THEN detail.amount ELSE 0 END) ) aug,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 7 THEN detail.amount ELSE 0 END) ) jul,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 6 THEN detail.amount ELSE 0 END) ) jun,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 5 THEN detail.amount ELSE 0 END) ) may,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 4 THEN detail.amount ELSE 0 END) ) apr,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 3 THEN detail.amount ELSE 0 END) ) mar,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 2 THEN detail.amount ELSE 0 END) ) feb,
       SUM( (CASE WHEN (EXTRACT month FROM bill.billdate) = 1 THEN detail.amount ELSE 0 END) ) jan
  FROM bmbill bill,
       bmbilldetail detail,
       membermaster master
 WHERE bill.intno = detail.intno
    AND bill.memberID = master.memberID
    AND bill.modified = 0
    AND bill.deleted = '0'
    AND detail.modified = 0
    AND detail.itemCode = 'G081'
 GROUP BY master.membername, bill.memberID

Open in new window

SOLUTION

jocave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial