Home » SQL & PL/SQL » SQL & PL/SQL » separate 2 columns into 2 rows
separate 2 columns into 2 rows [message #658858] Wed, 28 December 2016 09:58 Go to next message
wtolentino
Messages: 206
Registered: March 2005
Senior Member
i need to separate 2 columns into a 2 rows. i have done this in analytic query but it appears to be that our oracle forms builder does not recognize analytic query. please help. thanks.

sample data:
select ca.muni,
       ca.source_fy,
       ca.tif_amt,
       ca.laf_amt,
       ca.ctif_amt,
       ca.claf_amt
  from (select '002034'    muni,
               '2016'      source_fy,
               44938080.07 tif_amt,
               41033328.7  laf_amt,
               38907079.81 ctif_amt,
               35526182.43 claf_amt
          from dual       
        union all
        select '110000'   muni,
               '2016'     source_fy,
               1131714.06 tif_amt,
               1239792.02 laf_amt,
               979830.23  ctif_amt,
               1073397.62 claf_amt
          from dual) ca;

expected output:
muni   source_fy type allot_amt   
------ --------- ---- ----------- 
002034 2016      T    44938080.07 
002034 2016      L    41033328.7 
110000 2016      T    1131714.06
110000 2016      L    1239792.02
Re: separate 2 columns into 2 rows [message #658859 is a reply to message #658858] Wed, 28 December 2016 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version?

For all versions:
SQL> set numwidth 11
SQL> select muni, source_fy,
  2         decode(line, 1,'T', 2,'L') type,
  3         decode(line, 1,tif_amt, 2,laf_amt) allot_amt
  4    from (select '002034'    muni,
  5                 '2016'      source_fy,
  6                 44938080.07 tif_amt,
  7                 41033328.7  laf_amt,
  8                 38907079.81 ctif_amt,
  9                 35526182.43 claf_amt
 10            from dual
 11          union all
 12          select '110000'   muni,
 13                 '2016'     source_fy,
 14                 1131714.06 tif_amt,
 15                 1239792.02 laf_amt,
 16                 979830.23  ctif_amt,
 17                 1073397.62 claf_amt
 18            from dual) ca,
 19         (select 1 line from dual union all select 2 from dual) lines
 20  order by 1, 2, line
 21  /
MUNI   SOUR T   ALLOT_AMT
------ ---- - -----------
002034 2016 T 44938080.07
002034 2016 L  41033328.7
110000 2016 T  1131714.06
110000 2016 L  1239792.02

For 11g+ use UNPIVOT but your tools does not allow analytical functions then it won't allow UNPIVOT.

Re: separate 2 columns into 2 rows [message #659036 is a reply to message #658859] Wed, 04 January 2017 14:05 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Hi,

Similar to the previous post, but I rather than use the line number I inserted a "sort_order column to sort the output assuming that you will be selecting from a Table which may not contain ordered data. Added a 1 for "T" Types and a 2 for the "L" Types to allow sorting in the Order By.

Have a Great Day, Mike

SELECT muni "muni"
, source_fy "source_fy"
, type "type"
, allot_amt "allot_amt"
FROM (
SELECT ca.muni muni
, ca.source_fy source_fy
, 1 sort_order
, 'T' type
, ca.tif_amt allot_amt
from (select '002034' muni,
'2016' source_fy,
44938080.07 tif_amt,
41033328.7 laf_amt,
38907079.81 ctif_amt,
35526182.43 claf_amt
from dual
union all
select '110000' muni,
'2016' source_fy,
1131714.06 tif_amt,
1239792.02 laf_amt,
979830.23 ctif_amt,
1073397.62 claf_amt
from dual) ca
UNION ALL
SELECT ca.muni muni
, ca.source_fy source_fy
, 2 sort_order
, 'L' type
, ca.laf_amt allot_amt
from (select '002034' muni,
'2016' source_fy,
44938080.07 tif_amt,
41033328.7 laf_amt,
38907079.81 ctif_amt,
35526182.43 claf_amt
from dual
union all
select '110000' muni,
'2016' source_fy,
1131714.06 tif_amt,
1239792.02 laf_amt,
979830.23 ctif_amt,
1073397.62 claf_amt
from dual) ca
)
ORDER BY muni, source_fy,_sort_order;
Re: separate 2 columns into 2 rows [message #659041 is a reply to message #659036] Wed, 04 January 2017 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.

I don't know what you wanted to post (as it is unreadable) but it is invalid:
SQL> SELECT muni "muni"
  2  , source_fy "source_fy"
  3  , type "type"
  4  , allot_amt "allot_amt"
  5  FROM (
  6  SELECT ca.muni muni
  7  , ca.source_fy source_fy
  8  , 1 sort_order
  9  , 'T' type
 10  , ca.tif_amt allot_amt
 11  from (select '002034' muni,
 12  '2016' source_fy,
 13  44938080.07 tif_amt,
 14  41033328.7 laf_amt,
 15  38907079.81 ctif_amt,
 16  35526182.43 claf_amt
 17  from dual
 18  union all
 19  select '110000' muni,
 20  '2016' source_fy,
 21  1131714.06 tif_amt,
 22  1239792.02 laf_amt,
 23  979830.23 ctif_amt,
 24  1073397.62 claf_amt
 25  from dual) ca
 26  UNION ALL
 27  SELECT ca.muni muni
 28  , ca.source_fy source_fy
 29  , 2 sort_order
 30  , 'L' type
 31  , ca.laf_amt allot_amt
 32  from (select '002034' muni,
 33  '2016' source_fy,
 34  44938080.07 tif_amt,
 35  41033328.7 laf_amt,
 36  38907079.81 ctif_amt,
 37  35526182.43 claf_amt
 38  from dual
 39  union all
 40  select '110000' muni,
 41  '2016' source_fy,
 42  1131714.06 tif_amt,
 43  1239792.02 laf_amt,
 44  979830.23 ctif_amt,
 45  1073397.62 claf_amt
 46  from dual) ca
 47  )
 48  ORDER BY muni, source_fy,_sort_order;
ORDER BY muni, source_fy,_sort_order
                         *
ERROR at line 48:
ORA-00911: invalid character

[Updated on: Wed, 04 January 2017 14:59]

Report message to a moderator

Re: separate 2 columns into 2 rows [message #659045 is a reply to message #658858] Wed, 04 January 2017 21:41 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Hi,

Similar to the first post, but rather than using the line number I inserted a "sort_order" column in the Lines 8 and 29 to later sort the output in line 48. This is assuming that you will be selecting from a Table which may not contain ordered data. Added "sort_order" of 1 for "T" Types and a 2 for the "L" Types to use numerical sorting. Corrected Error and code is formatted from SQLPLUS. Hopefully a better looking outcome.


Another thought I had was to use the Query as the basis for a View and then Select from the View with in the Oracle Form.


Have a Great Day


SQL> SELECT muni "muni"
2 , source_fy "source_fy"
3 , type "type"
4 , allot_amt "allot_amt"
5 FROM (
6 SELECT ca.muni muni
7 , ca.source_fy source_fy
8 , 1 sort_order
9 , 'T' type
10 , ca.tif_amt allot_amt
11 from (select '002034' muni,
12 '2016' source_fy,
13 44938080.07 tif_amt,
14 41033328.7 laf_amt,
15 38907079.81 ctif_amt,
16 35526182.43 claf_amt
17 from dual
18 union all
19 select '110000' muni,
20 '2016' source_fy,
21 1131714.06 tif_amt,
22 1239792.02 laf_amt,
23 979830.23 ctif_amt,
24 1073397.62 claf_amt
25 from dual) ca
26 UNION ALL
27 SELECT ca.muni muni
28 , ca.source_fy source_fy
29 , 2 sort_order
30 , 'L' type
31 , ca.laf_amt allot_amt
32 from (select '002034' muni,
33 '2016' source_fy,
34 44938080.07 tif_amt,
35 41033328.7 laf_amt,
36 38907079.81 ctif_amt,
37 35526182.43 claf_amt
38 from dual
39 union all
40 select '110000' muni,
41 '2016' source_fy,
42 1131714.06 tif_amt,
43 1239792.02 laf_amt,
44 979830.23 ctif_amt,
45 1073397.62 claf_amt
46 from dual) ca
47 )
48 ORDER BY muni, source_fy, sort_order;

muni sour t allot_amt
------ ---- - ----------
002034 2016 T 44938080.1
002034 2016 L 41033328.7
110000 2016 T 1131714.06
110000 2016 L 1239792.02
Re: separate 2 columns into 2 rows [message #659047 is a reply to message #659045] Thu, 05 January 2017 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Wed, 04 January 2017 21:59

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
There is no need to add a column for your kind of solution but you scan twice the data so it is not a good solution.

Re: separate 2 columns into 2 rows [message #659221 is a reply to message #658858] Mon, 09 January 2017 17:01 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Re-Post of the original suggested query. Hopefully I have corrected my mistakes and the formatted output is
retained.

SQL> SET NUMWIDTH 12;
SQL>
SQL> SELECT   muni       "muni"
  2         , source_fy  "source_fy"
  3         , type       "type"
  4         , allot_amt  "allot_amt"
  5  FROM (
  6    SELECT   ca.muni       muni
  7           , ca.source_fy  source_fy
  8           , 1             sort_order
  9           , 'T'           type
 10           , ca.tif_amt    allot_amt
 11    from (select '002034'    muni,
 12                 '2016'      source_fy,
 13                 44938080.07 tif_amt,
 14                 41033328.7  laf_amt,
 15                 38907079.81 ctif_amt,
 16                 35526182.43 claf_amt
 17          from dual
 18          union all
 19          select '110000'   muni,
 20                 '2016'     source_fy,
 21                 1131714.06 tif_amt,
 22                 1239792.02 laf_amt,
 23                 979830.23  ctif_amt,
 24                 1073397.62 claf_amt
 25            from dual) ca
 26    UNION ALL
 27    SELECT   ca.muni       muni
 28           , ca.source_fy  source_fy
 29           , 2             sort_order
 30           , 'L'           type
 31           , ca.laf_amt    allot_amt
 32    from (select '002034'    muni,
 33                 '2016'      source_fy,
 34                 44938080.07 tif_amt,
 35                 41033328.7  laf_amt,
 36                 38907079.81 ctif_amt,
 37                 35526182.43 claf_amt
 38            from dual
 39          union all
 40          select '110000'   muni,
 41                 '2016'     source_fy,
 42                 1131714.06 tif_amt,
 43                 1239792.02 laf_amt,
 44                 979830.23  ctif_amt,
 45                 1073397.62 claf_amt
 46            from dual) ca
 47  )
 48  ORDER BY muni, source_fy, sort_order;

muni   sour t    allot_amt
------ ---- - ------------
002034 2016 T  44938080.07
002034 2016 L   41033328.7
110000 2016 T   1131714.06
110000 2016 L   1239792.02
Re: separate 2 columns into 2 rows [message #659222 is a reply to message #658858] Mon, 09 January 2017 17:11 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Another example using the UNPIVOT Command and using DECODE in conjunction with ROWNUM to derive the Column Type.

Unfortunately I could not find any good example of a multi-column output of the UNPIVOT command for this Post.

In this query each row from the source becomes 2 rows in the output. Each row in the Output
returns a ROWNUM. The Position of the columns in the IN Clause of the UNPIVOT (tif_amt, laf_amt)
determines the order of the records for each row of output from the source that is processed.
The MOD Function returns the remainder of the division is used to determine the value of the "TYPE" column.
in this case determine which type of record in the Output. Odd Records (MOD returns 1) are tif_amt
and the even records (MOD returns 0 ) are laf_amt records.

What I have observed is that additional columns can be added to the UNPIVOT. For example if a 3rd column
is added the IN clause changes to (tif_amt, laf_amt, 3rd column) and then the
DECODE(MOD(ROWNUM, 2), 1, 'T', 0, 'L') becomes DECODE(MOD(ROWNUM, 3), 1, 'T', 2, 'L', 0, '3rd Item')

From this Post I had an opportunity to learn and practice some new techniques.

SQL> SELECT
  2          muni
  3        , source_fy
  4        , allot_amt
  5        , DECODE(MOD(ROWNUM, 2), 1, 'T', 0, 'L') TYPE
  6  FROM  (select '002034'     muni,
  7                 '2016'      source_fy,
  8                 44938080.07 tif_amt,
  9                 41033328.7  laf_amt,
 10                 38907079.81 ctif_amt,
 11                 35526182.43 claf_amt
 12          from dual
 13          union all
 14          select '110000'    muni,
 15                 '2016'      source_fy,
 16                 1131714.06  tif_amt,
 17                 1239792.02  laf_amt,
 18                 979830.23   ctif_amt,
 19                 1073397.62  claf_amt
 20            from dual)
 21  UNPIVOT INCLUDE NULLS
 22    (allot_amt FOR amt_type IN (tif_amt, laf_amt))
 23  ORDER BY  muni
 24           ,source_fy
 25           ,ROWNUM;

MUNI   SOUR    ALLOT_AMT T
------ ---- ------------ -
002034 2016  44938080.07 T
002034 2016   41033328.7 L
110000 2016   1131714.06 T
110000 2016   1239792.02 L
Re: separate 2 columns into 2 rows [message #659234 is a reply to message #659222] Tue, 10 January 2017 03:42 Go to previous message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since the original problem was that forms didn't like the query I should point out that this issue can often by got around by putting the query in a view and querying the view from forms.
Previous Topic: GROUP BY COST CENTER
Next Topic: dependency question
Goto Forum:
  


Current Time: Fri Feb 23 10:52:15 CST 2018

Total time taken to generate the page: 0.02820 seconds