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  |
wtolentino
Messages: 398
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  |
 |
Michel Cadot
Messages: 68645
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 |
 |
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 |
|
Michel Cadot
Messages: 68645
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 |
|
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 #659221 is a reply to message #658858] |
Mon, 09 January 2017 17:01 |
|
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 |
|
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 |
cookiemonster
Messages: 13920
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.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 21:54:41 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
