Home » SQL & PL/SQL » SQL & PL/SQL » Transpose of row to column
Transpose of row to column [message #239532] Tue, 22 May 2007 10:45 Go to next message
Aju
Messages: 94
Registered: October 2004
Member
Hi

I need the output as in the attached file.

I have tried using the query but is there any other way as the domain values may get changed.



SELECT CAT_TYPE, COUNTRY_CODE,
SUM(DECODE(CURRENCY_CODE, 'USD', NETT_SETT_AMT, 0)) USD, SUM(DECODE(CURRENCY_CODE, 'JPY', NETT_SETT_AMT, 0)) JPY, SUM(DECODE(CURRENCY_CODE, 'GBP', NETT_SETT_AMT, 0)) GBP, SUM(DECODE(CURRENCY_CODE, 'EUR', NETT_SETT_AMT, 0)) EUR, SUM(DECODE(CURRENCY_CODE, 'CHF', NETT_SETT_AMT, 0)) CHF, SUM(DECODE(CURRENCY_CODE, 'CAD', NETT_SETT_AMT, 0)) CAD, SUM(DECODE(CURRENCY_CODE, 'MXN', NETT_SETT_AMT, 0)) MXN,
SUM(NETT_SETT_AMT) TOT_AMT
FROM NANIA
WHERE COUNTRY_CODE = 'US'
GROUP BY CAT_TYPE, COUNTRY_CODE
UNION ALL
SELECT CAT_TYPE, COUNTRY_CODE,
SUM(DECODE(CURRENCY_CODE, 'USD', NETT_SETT_AMT, 0)) USD, SUM(DECODE(CURRENCY_CODE, 'JPY', NETT_SETT_AMT, 0)) JPY, SUM(DECODE(CURRENCY_CODE, 'GBP', NETT_SETT_AMT, 0)) GBP, SUM(DECODE(CURRENCY_CODE, 'EUR', NETT_SETT_AMT, 0)) EUR, SUM(DECODE(CURRENCY_CODE, 'CHF', NETT_SETT_AMT, 0)) CHF, SUM(DECODE(CURRENCY_CODE, 'CAD', NETT_SETT_AMT, 0)) CAD, SUM(DECODE(CURRENCY_CODE, 'MXN', NETT_SETT_AMT, 0)) MXN,
SUM(NETT_SETT_AMT) TOT_AMT
FROM NANIA
WHERE COUNTRY_CODE = 'UK'
GROUP BY CAT_TYPE, COUNTRY_CODE
UNION ALL
SELECT CAT_TYPE, COUNTRY_CODE,
SUM(DECODE(CURRENCY_CODE, 'USD', NETT_SETT_AMT, 0)) USD, SUM(DECODE(CURRENCY_CODE, 'JPY', NETT_SETT_AMT, 0)) JPY, SUM(DECODE(CURRENCY_CODE, 'GBP', NETT_SETT_AMT, 0)) GBP, SUM(DECODE(CURRENCY_CODE, 'EUR', NETT_SETT_AMT, 0)) EUR, SUM(DECODE(CURRENCY_CODE, 'CHF', NETT_SETT_AMT, 0)) CHF, SUM(DECODE(CURRENCY_CODE, 'CAD', NETT_SETT_AMT, 0)) CAD, SUM(DECODE(CURRENCY_CODE, 'MXN', NETT_SETT_AMT, 0)) MXN,
SUM(NETT_SETT_AMT) TOT_AMT
FROM NANIA
WHERE COUNTRY_CODE = 'JP'
GROUP BY CAT_TYPE, COUNTRY_CODE



The other option I tried is

CREATE OR REPLACE PACKAGE cross_tab_query
AS
TYPE ref_cursor IS REF CURSOR;
END;

CREATE OR REPLACE PROCEDURE return_result(p_query out cross_tab_query.ref_cursor)
IS
V_select VARCHAR2(4000) := '' ;

BEGIN

FOR cur_rec IN (SELECT DISTINCT CURRENCY_CODE FROM NANIA)
LOOP

V_select := V_select ||
', ''' || cur_rec.CURRENCY_CODE || ''' as ' || cur_rec.CURRENCY_CODE || ',' ||
' (SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE AND B.COUNTRY_CODE = A.COUNTRY_CODE AND ' ||
' B.CURRENCY_CODE = ''' || cur_rec.CURRENCY_CODE || ''' ) as AMT' || cur_rec.CURRENCY_CODE || '' ;

END LOOP;

V_Select := 'SELECT CAT_TYPE,COUNTRY_CODE ' || V_select
|| ' FROM (SELECT DISTINCT CAT_TYPE,COUNTRY_CODE FROM NANIA) A ';
OPEN p_query for V_select;

END return_result;


which ended me getting the query

SELECT CAT_TYPE,COUNTRY_CODE , 'CAD' as CAD,
(SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE
AND B.COUNTRY_CODE = A.COUNTRY_CODE
AND B.CURRENCY_CODE = 'CAD' ) as AMTCAD,
'CHF' as CHF,
(SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE
AND B.COUNTRY_CODE = A.COUNTRY_CODE AND B.CURRENCY_CODE = 'CHF' ) as AMTCHF,
'EUR' as EUR, (SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE AND B.COUNTRY_CODE = A.COUNTRY_CODE AND B.CURRENCY_CODE = 'EUR' ) as AMTEUR, 'GBP' as GBP, (SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE AND B.COUNTRY_CODE = A.COUNTRY_CODE AND B.CURRENCY_CODE = 'GBP' ) as AMTGBP, 'JPY' as JPY, (SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE AND B.COUNTRY_CODE = A.COUNTRY_CODE AND B.CURRENCY_CODE = 'JPY' ) as AMTJPY, 'MXN ' as MXN , (SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE AND B.COUNTRY_CODE = A.COUNTRY_CODE AND B.CURRENCY_CODE = 'MXN ' ) as AMTMXN , 'USD' as USD, (SELECT NETT_SETT_AMT from NANIA B where B.CAT_TYPE = A.CAT_TYPE AND B.COUNTRY_CODE = A.COUNTRY_CODE AND B.CURRENCY_CODE = 'USD' ) as AMTUSD
FROM (SELECT DISTINCT CAT_TYPE,COUNTRY_CODE FROM NANIA) A


Can you please guide me on the right approach

Thanks
Re: Transpose of row to column [message #239544 is a reply to message #239532] Tue, 22 May 2007 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now you know how to format, modify your post with format.
And add your Oracle version.

Regards
Michel
Re: Transpose of row to column [message #239801 is a reply to message #239532] Wed, 23 May 2007 06:05 Go to previous message
Snowblitzz
Messages: 8
Registered: May 2007
Junior Member
so you want to make a selection from a table and you want to count the rows.. or at least thats what i get out of it.


i would make it the following:

select cat_type, country_code, currency_code, sum(NETT_SETT_AMT)
from nania
group by cat_type, country_code, currency_code

with this you will make a sum of the nett_sett_amt per currency per country per cattype.
Previous Topic: Easy update (but not for me:(
Next Topic: finding the nth salary
Goto Forum:
  


Current Time: Tue Dec 06 04:42:07 CST 2016

Total time taken to generate the page: 0.29804 seconds