Home » SQL & PL/SQL » SQL & PL/SQL » Convert Rows into Columns (Oracle 9i)
| Convert Rows into Columns [message #311348] |
Fri, 04 April 2008 03:48  |
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
|
Junior Member |
|
|
Hi,
I have a query with seven rows which has columns like Amount. In that amount column data, one amount denoted as Total_Amount, then two amounts are TDS_Amount, next two amounts are Surcharge_Amount and then last two amounts are Cess_Amount. like
Amount
-------
10000 (Total_Amount)
1000 (TDS_Amount)
-1000 (TDS_Amount Reverse Entry)
100 (Surcharge_Amount)
-100 (Surcharge_Amount Reverse Entry)
10 (Cess_Amount)
-10 (Cess_Amount Reverse Entry)
Now I want this Amount in columns, thats y I have used In-line view in the query and get the output like :
Amount TDS_Amount Surchaege_Amount Cess_Amount
------ ---------- ----------------- ----------
10000 1000 100 10
10000 -1000 100 10
10000 1000 -100 10
10000 1000 100 -10
10000 -1000 100 -10
10000 -1000 -100 10
10000 -1000 -100 -10
But I wants output like this
Amount TDS_Amount Surchaege_Amount Cess_Amount
------ ---------- ----------------- ----------
10000 1000 100 10
10000 -1000 -100 -10
my query is :
select /*+ choose */
distinct stdapi.invoice_id,
(tds.TDS_amt) TDS_amt,
(surcharg.Surcharge_amt) Surcharge_amt,
(cess.Education_Cess_amt) Education_Cess_amt ,
(tds.TDS_amt) + (surcharg.Surcharge_amt) + (cess.Education_Cess_amt) Total_amount,
tds.name tds_name,
Surcharg.name surchrg_name,
cess.name cess_name,
apc.tax_rate
from
( select distinct apc.invoice_id,
tds_invoice_id,
apc1.tax_rate, apc.
certificate_id
from apc_tds_detail@qu apc,
apc_tds_header@qu apc1
where
trunc(apc1.creation_date) >='11-feb-2008'
and apc.certificate_id = apc1.certificate_id
) apc,
( SELECT api.invoice_amount TDS_amt,
atc.name,
api.invoice_id inv_id,
stdapi.invoice_id std_inv_id
FROM (select distinct tds_invoice_id,invoice_id,certificate_id,creation_date
from apc_tds_detail@qu )apc,
ap_invoices_all@qu stdapi,
ap_invoices_all@qu api,
ap_tax_codes_all@qu atc,
ap_invoice_distributions_all@qu stdapid
WHERE
TRUNC (apc.creation_date) >= '11-feb-2008'
and stdapi.invoice_id= apc.invoice_id
and apc.tds_invoice_id = api.invoice_id
and stdapid.invoice_id = stdapi.invoice_id
and stdapid.tax_code_id = atc.tax_id
and stdapid.awt_invoice_id= apc.tds_invoice_id
and atc.name like '%-194%-B' and stdapi.invoice_id = '6313308'
) tds,
( SELECT api.invoice_amount Surcharge_amt,
atc.name,
api.invoice_id inv_id,
stdapi.invoice_id std_inv_id
FROM (select distinct tds_invoice_id,invoice_id,certificate_id,creation_date
from apc_tds_detail@qu )apc,
ap_invoices_all@qu stdapi,
ap_invoices_all@qu api,
ap_tax_codes_all@qu atc,
ap_invoice_distributions_all@qu stdapid
WHERE
TRUNC (apc.creation_date) >= '11-feb-2008'
and stdapi.invoice_id= apc.invoice_id
and apc.tds_invoice_id = api.invoice_id
and stdapid.invoice_id = stdapi.invoice_id
and stdapid.tax_code_id = atc.tax_id
and stdapid.awt_invoice_id= apc.tds_invoice_id
and atc.name like '%-194%-S' and stdapi.invoice_id = '6313308'
) surcharg,
( SELECT api.invoice_amount Education_Cess_amt,
atc.name,
api.invoice_id inv_id,
stdapi.invoice_id std_inv_id
FROM (select distinct tds_invoice_id,invoice_id,certificate_id,creation_date
from apc_tds_detail@qu )apc,
ap_invoices_all@qu stdapi,
ap_invoices_all@qu api,
ap_tax_codes_all@qu atc,
ap_invoice_distributions_all@qu stdapid
WHERE
TRUNC (apc.creation_date) >= '11-feb-2008'
and stdapi.invoice_id= apc.invoice_id
and apc.tds_invoice_id = api.invoice_id
and stdapid.invoice_id = stdapi.invoice_id
and stdapid.tax_code_id = atc.tax_id
and stdapid.awt_invoice_id= apc.tds_invoice_id
and atc.name like '%-194%-E' and stdapi.invoice_id = '6313308'
) cess,
po_vendors@qu pov,
po_vendor_sites_all@qu povs ,
po_vendors@qu pov1,
po_vendor_sites_all@qu povs1 ,
ap_invoices_all@qu stdapi,
ap_invoice_distributions_all@qu stdapid,
ap_invoices_all@qu api,
gl_code_combinations@qu gcc,
ap_awt_groups@qu apg,
ap_tax_codes_all@qu atc
where 1 = 1
and stdapi.invoice_id = stdapid.invoice_id
and apc.tds_invoice_id=api.invoice_id
and apc.invoice_id = stdapi.invoice_id
and pov.vendor_id = stdapi.vendor_id
and povs.vendor_site_id = stdapi.vendor_site_id
and api.vendor_id=pov1.vendor_id
and api.vendor_site_id=povs1.vendor_site_id
and gcc.code_combination_id = stdapid.dist_code_combination_id
and pov.vendor_id = povs.vendor_id
and pov1.vendor_id = povs1.vendor_id
and apg.group_id = stdapid.awt_origin_group_id
and stdapid.awt_invoice_id=apc.tds_invoice_id
and atc.tax_id = stdapid.tax_code_id
and cess.std_inv_id = stdapi.invoice_id
and surcharg.std_inv_id = stdapi.invoice_id
and tds.std_inv_id = stdapi.invoice_id
and stdapi.invoice_id = '6313308'
group by stdapi.invoice_id,
(tds.TDS_amt) ,
(surcharg.Surcharge_amt) ,
(cess.Education_Cess_amt) ,
(tds.TDS_amt) + (surcharg.Surcharge_amt) + (cess.Education_Cess_amt) ,
tds.name ,
Surcharg.name ,
cess.name ,
apc.tax_rate
Please do not use MAX() and MIN() function to get the output. I have already try it sucessfully but as per my Manager I should get output without the use of MIN() and MAX() function
|
|
|
|
| Re: Convert Rows into Columns [message #311354 is a reply to message #311348] |
Fri, 04 April 2008 04:21  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Did your manager authorised you to use the select statement or you should be writing your own statement?
O.k now coming back to your data if you don't mind can you post some test data and also spend some time reading the forum guidelines. It will be much appreciated.
Regards
Raj
|
|
|
|
|
|
| Re: Convert Rows into Columns [message #311426 is a reply to message #311348] |
Fri, 04 April 2008 08:15 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| mehulmb wrote on Fri, 04 April 2008 04:48 |
WHERE
TRUNC (apc.creation_date) >= '11-feb-2008'
|
This is an invalid use of DATEs in Oracle. You need to use a TO_DATE function, which can be readily found in the documentation.
|
|
|
|
| Re: Convert Rows into Columns [message #311560 is a reply to message #311348] |
Fri, 04 April 2008 15:18 |
nitinkumar_007
Messages: 18
Registered: January 2008
Location: PUNE
|
Junior Member |
|
|
hi ,
Use decode option..
Amount Code
10 cq
20 lp
30 ad
select decode(b,'cq',a,0)cq_amount
,decode(b,'lp',a,0) lp_amount
,decode(b,'ad',a,0) ad_amount
from x
this way you can convert rows into column
Regards
Nitin
[Updated on: Fri, 04 April 2008 15:21] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Jul 18 03:24:11 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
