oracle show table like cartesian coordinate system

Question

I have a table

create table test_table(
id number(10),
x number(10),
y number(10),
svalue number(10));

with filling a table as

declare
    i integer;
begin
    i := 0;
    for x in 1 .. 10 loop
        for y in 1 .. 10 loop
            i := i + 1;
            insert into test_table
                (Id, x, y, svalue)
            values
                (i, x, y, x + y);
        end loop;
    end loop;
    commit;
end;

how I can show table like

    1 2 3 4 5 Ny
  1 2 3 4 5 6 
  2 3 4 5 6 7
  3 4 5 6 7 8
  Nx

where x - rows, y - columns, svalue - value x,y

ref this url first http://stackoverflow.com/questions/38651147/how-to-transpose-column-into-row-in-oracle-sql-11g/38653133#38653133 and after create pivot function run this query **select * from table(pivot('select x,y,svalue from test_table'))** — Sanjay Radadiya, Aug 04 '16 at 08:42
I have **Oracle Database 11g Express Edition Release 11.2.0.2.0** and **ORA-29913: error in executing ODCITABLEDESCRIBE callout** — osydorchuk, Aug 04 '16 at 09:24
if you table data as you maintain in question then it will work. — Sanjay Radadiya, Aug 04 '16 at 09:28
I runned code from a question and got an error and I found out **error usually happens in versions before 11.2.0.3** — osydorchuk, Aug 04 '16 at 09:44
check my answers http://stackoverflow.com/questions/38761959/oracle-show-table-like-cartesian-coordinate-system/38764057#38764057 — Sanjay Radadiya, Aug 04 '16 at 09:59
Thank you, your answers very useful. Can you write your first-second comment as answer and I mark him as correct. — osydorchuk, Aug 04 '16 at 11:52

Sanjay Radadiya · Answer 1 · 2016-08-04T09:57:34.467

Hear i achieve cartesian coordinate system using looping test_table.

declare
    HEAD VARCHAR2(100);
    CURSOR c1 IS SELECT distinct x rec FROM test_table order by x;
    CURSOR c2 IS SELECT distinct y rec FROM test_table order by y;
begin

     -- for disply header in y  
     for y in c2 loop
       HEAD := HEAD || lpad(y.rec,4);
     end loop;
     DBMS_OUTPUT.put_line( lpad('X',3) || HEAD );
    --

    -- disply value with repect to x and y
    for x in c1 loop
        declare
        STR VARCHAR2(100);
        CURSOR c2 IS SELECT svalue rec FROM test_table where x= x.rec order by y;
        begin
          for y in c2 loop
           STR := str || lpad(y.rec,4);
          end loop;
          DBMS_OUTPUT.put_line(lpad(x.rec,3) || STR);
        end;
    end loop;
    --
end;

i hope this will help.

score 1 · Accepted Answer · answered Aug 04 '16 at 12:00

1

if we want to get pivot output for Cartesian coordinate system

run below script for create pivot function http://paste.ubuntu.com/21378705/

pivot function is actually ref cursor that give dynamic column output after ruining above script run query as

select * from table(pivot('select x,y,svalue from test_table'))

in above query select statement use as following manner

select rowsection,columnsection,data from table

i hope this will help.

answered Aug 04 '16 at 12:00

Sanjay Radadiya

1,254
15
22

It's possible to change something in http://paste.ubuntu.com/21378705/ for using data string with space as column name? – osydorchuk Aug 10 '16 at 08:47

oracle show table like cartesian coordinate system

2 Answers2