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 Go to next message
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 Go to previous messageGo to next message
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 #311355 is a reply to message #311348] Fri, 04 April 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Why not use MIN or MAX if it fits your requirements?

Regards
Michel
Re: Convert Rows into Columns [message #311426 is a reply to message #311348] Fri, 04 April 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4643
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 Go to previous messageGo to next message
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

Re: Convert Rows into Columns [message #311562 is a reply to message #311560] Fri, 04 April 2008 15:33 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nitin,

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Define a Record Layout (merged 3 topics, deleted 2)
Next Topic: replace the column value
Goto Forum:
  


Current Time: Thu Dec 08 20:14:39 CST 2016

Total time taken to generate the page: 0.15329 seconds