Home » SQL & PL/SQL » SQL & PL/SQL » Displaying select results in double columns (11.2.0.1.0)
Displaying select results in double columns [message #680350] |
Sat, 09 May 2020 01:58 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I hope everybody is well and safe together with their beloved ones.
I am not sure if this is possible or not, but I am trying to send emails from oracle that contain tables. In order to have the data more presentable, I am trying to reduce the size of the displayed table by showing two rows in one row (duplicate fields).
so in the following example:
create table test_all_inst
(
ser_no number,
value number
);
insert all
into TEST_ALL_INST(ser_no, VALUE) values (1, 500)
into TEST_ALL_INST(ser_no, VALUE) values (2, 500)
into TEST_ALL_INST(ser_no, VALUE) values (3, 500)
into TEST_ALL_INST(ser_no, VALUE) values (4, 500)
into TEST_ALL_INST(ser_no, VALUE) values (5, 500)
into TEST_ALL_INST(ser_no, VALUE) values (6, 500)
into TEST_ALL_INST(ser_no, VALUE) values (7, 500)
into TEST_ALL_INST(ser_no, VALUE) values (8, 500)
into TEST_ALL_INST(ser_no, VALUE) values (9, 500)
into TEST_ALL_INST(ser_no, VALUE) values (10, 500)
into TEST_ALL_INST(ser_no, VALUE) values (11, 500)
into TEST_ALL_INST(ser_no, VALUE) values (12, 500)
into TEST_ALL_INST(ser_no, VALUE) values (13, 500)
into TEST_ALL_INST(ser_no, VALUE) values (14, 500)
into TEST_ALL_INST(ser_no, VALUE) values (15, 500)
into TEST_ALL_INST(ser_no, VALUE) values (16, 500)
into TEST_ALL_INST(ser_no, VALUE) values (17, 500)
into TEST_ALL_INST(ser_no, VALUE) values (18, 500)
into TEST_ALL_INST(ser_no, VALUE) values (19, 500)
into TEST_ALL_INST(ser_no, VALUE) values (20, 500)
into TEST_ALL_INST(ser_no, VALUE) values (21, 500)
select * from dual;
I instead of:
select * from TEST_ALL_INST;
SER_NO VALUE
1 500
2 500
3 500
4 500
5 500
6 500
7 500
8 500
9 500
10 500
11 500
12 500
13 500
14 500
15 500
16 500
17 500
18 500
19 500
20 500
21 500
I need:
SER_NO VALUE SER_NO VALUE
1 500 12 500
2 500 13 500
3 500 14 500
4 500 15 500
5 500 16 500
6 500 17 500
7 500 18 500
8 500 19 500
9 500 20 500
10 500 21 500
11 500
Thanks,
Ferro
|
|
|
Re: Displaying select results in double columns [message #680351 is a reply to message #680350] |
Sat, 09 May 2020 02:21 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Is this horrible structure any use?orclz> ed
Wrote file afiedt.buf
1 with even as (select rownum join_col,ser_no,value from (select rownum rn,rownum/2 half_rn,ser_no,value from test_all_inst) where half_rn=trunc(half_rn)),
2 odd as (select rownum join_col,ser_no,value from (select rownum rn,rownum/2 half_rn,ser_no,value from test_all_inst) where half_rn<>trunc(half_rn))
3* select odd.ser_no,odd.value,even.ser_no,even.value from odd join even using(join_col)
orclz> /
SER_NO VALUE SER_NO VALUE
---------- ---------- ---------- ----------
1 500 2 500
3 500 4 500
5 500 6 500
7 500 8 500
9 500 10 500
11 500 12 500
13 500 14 500
15 500 16 500
17 500 18 500
19 500 20 500
10 rows selected.
orclz> I'm sure that a real solution would use analytic functions.
|
|
|
Re: Displaying select results in double columns [message #680352 is a reply to message #680351] |
Sat, 09 May 2020 02:30 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
@John Watson,
Thanks a lot for the reply. But this solution does not show the extra odd column (for ser_no 21).
I added the left outer join instead:
WITH
even AS
(SELECT ROWNUM join_col,
ser_no,
value
FROM (SELECT ROWNUM rn,
ROWNUM / 2 half_rn,
ser_no,
value
FROM test_all_inst)
WHERE half_rn = TRUNC(half_rn)
),
odd AS
(SELECT ROWNUM join_col,
ser_no,
value
FROM (SELECT ROWNUM rn,
ROWNUM / 2 half_rn,
ser_no,
value
FROM test_all_inst)
WHERE half_rn <> TRUNC(half_rn)
)
SELECT odd.ser_no,
odd.value,
even.ser_no,
even.value
FROM odd
left outer JOIN even
USING (join_col);
I also do not get what you mean by analytic function, you mean use analytic function to get odd and even rows instead of trunc on rownum?
Thanks,
Ferro
[Updated on: Sat, 09 May 2020 02:33] Report message to a moderator
|
|
|
|
Re: Displaying select results in double columns [message #680354 is a reply to message #680352] |
Sat, 09 May 2020 03:00 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:I also do not get what you mean by analytic function, you mean use analytic function to get odd and even rows instead of trunc on rownum? My abilities with analytic functions are close to zero. I do know that they can be used to navigate from one row to another, which may be what you need.
|
|
|
|
|
|
|
Re: Displaying select results in double columns [message #680359 is a reply to message #680358] |
Sat, 09 May 2020 03:40 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
@Michel
Sorry for the wrong wording, my mistake. I need the 1st half (or half + 1 in case of odd number of rows) on one side and the 2nd half on the other side.
The wording is wrong but the result I posted is what I want:
SER_NO VALUE SER_NO VALUE
1 500 12 500
2 500 13 500
3 500 14 500
4 500 15 500
5 500 16 500
6 500 17 500
7 500 18 500
8 500 19 500
9 500 20 500
10 500 21 500
11 500
Thanks,
Ferro
[Updated on: Sat, 09 May 2020 03:41] Report message to a moderator
|
|
|
Re: Displaying select results in double columns [message #680360 is a reply to message #680359] |
Sat, 09 May 2020 03:49 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Well working on the first issue (odd on one side and even on the other one) I removed some rows of your table:
SQL> select * from TEST_ALL_INST order by ser_no;
SER_NO VALUE
---------- ----------
1 500
2 500
3 500
4 500
5 500
7 500
8 500
10 500
11 500
13 500
14 500
15 500
16 500
17 500
18 500
20 500
21 500
17 rows selected.
So:
SQL> with
2 data as (
3 select ser_no, value,
4 case
5 when row_number() over (order by ser_no) <= (count(*) over()+1) / 2
6 then 1
7 else 2
8 end col,
9 case
10 when row_number() over (order by ser_no) <= (count(*) over()+1) / 2
11 then row_number() over (order by ser_no)
12 else row_number() over (order by ser_no) - trunc((count(*) over()+1)/2)
13 end rk
14 from TEST_ALL_INST
15 )
16 select max(decode(col, 1, ser_no)) ser_no,
17 max(decode(col, 1, value)) value,
18 max(decode(col, 2, ser_no)) ser_no,
19 max(decode(col, 2, value)) value
20 from data
21 group by rk
22 order by rk
23 /
SER_NO VALUE SER_NO VALUE
---------- ---------- ---------- ----------
1 500 13 500
2 500 14 500
3 500 15 500
4 500 16 500
5 500 17 500
7 500 18 500
8 500 20 500
10 500 21 500
11 500
9 rows selected.
If you are interested on this first issue:
SQL> with
2 data as (
3 select ser_no, value,
4 dense_rank() over (order by trunc((ser_no-1)/2)) rk
5 from TEST_ALL_INST
6 )
7 select max(decode(mod(ser_no,2), 1, ser_no)) ser_no,
8 max(decode(mod(ser_no,2), 1, value)) value,
9 max(decode(mod(ser_no,2), 0, ser_no)) ser_no,
10 max(decode(mod(ser_no,2), 0, value)) value
11 from data
12 group by rk
13 order by rk
14 /
SER_NO VALUE SER_NO VALUE
---------- ---------- ---------- ----------
1 500 2 500
3 500 4 500
5 500
7 500 8 500
10 500
11 500
13 500 14 500
15 500 16 500
17 500 18 500
20 500
21 500
11 rows selected.
[Updated on: Sat, 09 May 2020 03:51] Report message to a moderator
|
|
|
Re: Displaying select results in double columns [message #680362 is a reply to message #680360] |
Sat, 09 May 2020 05:08 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
@Michel
Thanks a million, this is new dimension to me.
For the sake of having a more generic statement (adding the case of first serial number that is not 1 and is even - for example remaining installment numbers) I modified ser_no to rownum as below:
with
data as (
select ser_no, mydate, value,
dense_rank() over (order by trunc((ROWNUM-1)/2)) rk
from TEST_ALL_INST
)
select max(decode(mod(ser_no,2), 1, ser_no)) ser_no,
max(decode(mod(ser_no,2), 1, mydate)) mydate,
max(decode(mod(ser_no,2), 1, value)) value,
max(decode(mod(ser_no,2), 0, ser_no)) "ser_no ",
max(decode(mod(ser_no,2), 0, mydate)) "mydate ",
max(decode(mod(ser_no,2), 0, value)) "value "
from data
group by rk
order by rk;
Thanks again,
Ferro
|
|
|
Re: Displaying select results in double columns [message #680364 is a reply to message #680360] |
Sat, 09 May 2020 05:09 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Or compacting:
SQL> with
2 data as (
3 select ser_no, value,
4 row_number() over (partition by trunc((ser_no-1)/2) order by ser_no) col
5 from TEST_ALL_INST
6 ),
7 data2 as (
8 select ser_no, value, col,
9 dense_rank() over (partition by col order by ser_no) rk
10 from data
11 )
12 select max(decode(mod(col,2), 1, ser_no)) ser_no,
13 max(decode(mod(col,2), 1, value)) value,
14 max(decode(mod(col,2), 0, ser_no)) ser_no,
15 max(decode(mod(col,2), 0, value)) value
16 from data2
17 group by rk
18 order by rk
19 /
SER_NO VALUE SER_NO VALUE
---------- ---------- ---------- ----------
1 500 2 500
3 500 4 500
5 500 8 500
7 500 14 500
10 500 16 500
11 500 18 500
13 500
15 500
17 500
20 500
21 500
11 rows selected.
|
|
|
|
|
Re: Displaying select results in double columns [message #680370 is a reply to message #680367] |
Sat, 09 May 2020 08:01 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
That is something like (for the question first half in first column and second half in the second one):
SQL> with
2 TEST_ALL_INST as (
3 select trunc(dbms_random.value(1,1000)) ser_no,
4 trunc(dbms_random.value(1000,5000)) value
5 from dual
6 connect by level < 20
7 ),
8 step1 as (
9 select ser_no, value,
10 row_number() over (order by ser_no) rownb,
11 count(*) over () + 1 cnt
12 from TEST_ALL_INST
13 ),
14 step2 as (
15 select ser_no, value,
16 case
17 when rownb <= cnt/2 then 1
18 else 2
19 end col,
20 case
21 when rownb <= cnt/2 then rownb
22 else rownb - trunc(cnt/2)
23 end rk
24 from step1
25 )
26 select max(decode(col, 1, ser_no)) ser_no,
27 max(decode(col, 1, value)) value,
28 max(decode(col, 2, ser_no)) ser_no,
29 max(decode(col, 2, value)) value
30 from step2
31 group by rk
32 order by rk
33 /
SER_NO VALUE SER_NO VALUE
---------- ---------- ---------- ----------
8 1705 484 1192
43 1004 489 2819
263 3220 604 2227
266 4667 680 3416
280 3006 787 2852
316 3929 811 1773
318 2336 916 1334
345 2140 957 1047
398 3253 964 3457
430 1816
10 rows selected.
|
|
|
|
Re: Displaying select results in double columns [message #680381 is a reply to message #680370] |
Sun, 10 May 2020 09:20 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Or simpler:
with test_all_inst as (
select trunc(dbms_random.value(1,1000)) ser_no,
trunc(dbms_random.value(1000,5000)) value
from dual
connect by level < 20
),
step1 as (
select ser_no,
value,
mod(row_number() over (order by ser_no) - 1,ceil(count(*) over () / 2)) rn,
ceil(row_number() over (order by ser_no) / ceil(count(*) over () / 2)) partition
from test_all_inst
)
select first_ser_no,
first_value,
second_ser_no,
second_value
from step1
pivot(
max(ser_no) ser_no,
max(value) value
for partition in (1 first,2 second)
)
order by rn
/
FIRST_SER_NO FIRST_VALUE SECOND_SER_NO SECOND_VALUE
------------ ----------- ------------- ------------
62 2334 536 3935
70 4220 693 4940
205 1203 733 3813
286 4562 802 1930
432 3692 817 3711
442 4222 817 3722
450 2728 870 4760
467 4768 899 1982
478 2961 922 1458
491 2433
10 rows selected.
SQL>
SY.
|
|
|
Re: Displaying select results in double columns [message #680387 is a reply to message #680381] |
Mon, 11 May 2020 05:12 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
And if OP would upgrade to supported version then match recognize would be even simpler solution:
with test_all_inst as (
select trunc(dbms_random.value(1,1000)) ser_no,
trunc(dbms_random.value(1000,5000)) value
from dual
connect by level < 20
)
select *
from test_all_inst
match_recognize(
order by ser_no
measures
one.ser_no first_ser_no,
one.value first_value,
two.ser_no second_ser_no,
two.value second_value
pattern(one two?)
define one as (count(*) = 1)
)
/
FIRST_SER_NO FIRST_VALUE SECOND_SER_NO SECOND_VALUE
------------ ----------- ------------- ------------
199 2237 273 2373
413 2375 461 1882
472 4462 550 4964
596 1115 601 1018
639 2653 658 4793
699 2122 729 2561
829 1345 850 2917
884 2166 903 2846
921 1629 945 4373
956 3663
10 rows selected.
SQL>
SY.
|
|
|
|
|
Re: Displaying select results in double columns [message #680394 is a reply to message #680392] |
Mon, 11 May 2020 06:04 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
with test_all_inst as (
select trunc(dbms_random.value(1,1000)) ser_no,
trunc(dbms_random.value(1000,5000)) value
from dual
connect by level < 20
),
step1 as (
select ser_no,
value,
mod(row_number() over (order by ser_no) - 1,ceil(count(*) over () / 2)) rn,
ceil(row_number() over (order by ser_no) / ceil(count(*) over () / 2)) partition
from test_all_inst
)
select *
from step1
match_recognize(
order by rn,
partition
measures
one.ser_no first_ser_no,
one.value first_value,
two.ser_no second_ser_no,
two.value second_value
pattern(one two?)
define one as (count(*) = 1)
)
/
SY.
|
|
|
Re: Displaying select results in double columns [message #680395 is a reply to message #680394] |
Mon, 11 May 2020 06:07 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Actually, pattern one condition is much simpler:
with test_all_inst as (
select trunc(dbms_random.value(1,1000)) ser_no,
trunc(dbms_random.value(1000,5000)) value
from dual
connect by level < 20
),
step1 as (
select ser_no,
value,
mod(row_number() over (order by ser_no) - 1,ceil(count(*) over () / 2)) rn,
ceil(row_number() over (order by ser_no) / ceil(count(*) over () / 2)) partition
from test_all_inst
)
select *
from step1
match_recognize(
order by rn,
partition
measures
one.ser_no first_ser_no,
one.value first_value,
two.ser_no second_ser_no,
two.value second_value
pattern(one two?)
define one as (1 = 1)
)
/
FIRST_SER_NO FIRST_VALUE SECOND_SER_NO SECOND_VALUE
------------ ----------- ------------- ------------
97 1544 585 2189
131 1589 636 1896
163 2812 660 3897
248 1638 662 2038
253 4078 740 1954
331 2956 761 3079
395 1875 944 4544
399 2969 955 3063
467 3513 974 4924
518 2026
10 rows selected.
SQL>
SY.
|
|
|
Re: Displaying select results in double columns [message #680397 is a reply to message #680395] |
Tue, 12 May 2020 02:59 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
OK, thanks, I admit I (wrongly) didn't investigate on this new feature which seems to be very powerful as I already saw it in your posts.
Is this possible (if you have time) to put the even ser_no in one column and the odd ones in the other columns like this?
SQL> with
2 TEST_ALL_INST as (
3 select trunc(dbms_random.value(1,1000)) ser_no,
4 trunc(dbms_random.value(1000,5000)) value
5 from dual
6 connect by level < 20
7 ),
8 step1 as (
9 select ser_no, value, mod(ser_no,2)+1 col,
10 dense_rank() over (partition by mod(ser_no,2) order by ser_no) rk
11 from TEST_ALL_INST
12 )
13 select max(decode(col, 1, ser_no)) ser_no,
14 max(decode(col, 1, value)) value,
15 max(decode(col, 2, ser_no)) ser_no,
16 max(decode(col, 2, value)) value
17 from step1
18 group by rk
19 order by rk
20 /
SER_NO VALUE SER_NO VALUE
---------- ---------- ---------- ----------
58 2919 23 3346
128 4233 127 4312
372 4155 233 3141
532 2356 235 4454
534 4043 443 1042
542 4699 547 4610
652 3466 597 3132
698 4213 923 3983
856 4907
910 4929
954 3942
11 rows selected.
|
|
|
Re: Displaying select results in double columns [message #680399 is a reply to message #680397] |
Tue, 12 May 2020 07:46 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Match recognize doesn't allow expressions in ORDER BY clause so we still need step1:
with test_all_inst as (
select trunc(dbms_random.value(1,1000)) ser_no,
trunc(dbms_random.value(1000,5000)) value
from dual
connect by level < 20
),
step1 as (
select ser_no,
value,
row_number() over (partition by mod(ser_no,2) order by ser_no) + mod(ser_no,2) / 2 rn
from test_all_inst
)
select *
from step1
match_recognize(
order by rn
measures
even.ser_no first_ser_no,
even.value first_value,
odd.ser_no second_ser_no,
odd.value second_value
pattern(even? odd?)
define even as (mod(ser_no,2) = 0),
odd as (mod(ser_no,2) = 1)
)
/
FIRST_SER_NO FIRST_VALUE SECOND_SER_NO SECOND_VALUE
------------ ----------- ------------- ------------
250 2713 33 2907
258 3712 55 1149
414 1857 63 1435
440 3181 89 4963
456 2833 161 4331
734 1522 199 1037
906 1791 337 3959
988 1229 383 3469
441 1925
993 1304
993 4728
11 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:25:12 CDT 2024
|