Home > Enterprise >  represent vertical output horizontally in oracle
represent vertical output horizontally in oracle

Time:09-05

my display cade looks like below i want to covert my display from vertical to horizontal
code:-

dbms_output.put_line('output1         : ' || value1);      
dbms_output.put_line('output2         : ' || value2);      
dbms_output.put_line('output3         : ' || value3);      
dbms_output.put_line('output4         : ' || value4);      
dbms_output.put_line('output5         : ' || value5);      
dbms_output.put_line('output6         : ' || value6);     

 output:-     

output1        : 100      
output2        : 200      
output3        : 300      
output4        : 400      
output5        : 500      
output6        : 600  

I want output like below:-

output1 output2 output3 output4 output5 output6
100 200 300 400 500 600

CodePudding user response:

As a first step, you will need to assign rows to the columns

select output1,output2,....., row_number() over (partition by value1 ,....order by output) col from yourtable

then you add PIVOT:

PIVOT
(
max(output)
for col in (1, 2, 3, ..., 240)
)```

CodePudding user response:

You can use LPAD:

DECLARE
  value1 NUMBER(8,2) := 100;
  value2 NUMBER(8,2) := 200;
  value3 NUMBER(8,2) := 300;
  value4 NUMBER(8,2) := 400;
  value5 NUMBER(8,2) := 500;
  value6 NUMBER(8,2) := 600;
  c_width  CONSTANT PLS_INTEGER := 10;
BEGIN
  dbms_output.put_line(
    LPAD('output1', c_width, ' ')
    || ' ' || LPAD('output2', c_width, ' ')
    || ' ' || LPAD('output3', c_width, ' ')
    || ' ' || LPAD('output4', c_width, ' ')
    || ' ' || LPAD('output5', c_width, ' ')
    || ' ' || LPAD('output6', c_width, ' ')
  );      
  dbms_output.put_line(
    LPAD(value1, c_width, ' ')
    || ' ' || LPAD(value2, c_width, ' ')
    || ' ' || LPAD(value3, c_width, ' ')
    || ' ' || LPAD(value4, c_width, ' ')
    || ' ' || LPAD(value5, c_width, ' ')
    || ' ' || LPAD(value6, c_width, ' ')
  );
END;
/

Which outputs:

   output1    output2    output3    output4    output5    output6
       100        200        300        400        500        600

or TO_CHAR:

DECLARE
  value1 NUMBER(8,2) := 100;
  value2 NUMBER(8,2) := 200;
  value3 NUMBER(8,2) := 300;
  value4 NUMBER(8,2) := 400;
  value5 NUMBER(8,2) := 500;
  value6 NUMBER(8,2) := 600;
  c_width  CONSTANT PLS_INTEGER := 10;
  c_format CONSTANT VARCHAR2(10) := '999999.99';
BEGIN
  dbms_output.put_line(
    LPAD('output1', c_width, ' ')
    || ' ' || LPAD('output2', c_width, ' ')
    || ' ' || LPAD('output3', c_width, ' ')
    || ' ' || LPAD('output4', c_width, ' ')
    || ' ' || LPAD('output5', c_width, ' ')
    || ' ' || LPAD('output6', c_width, ' ')
  );      
  dbms_output.put_line(
    TO_CHAR(value1, c_format)
    || ' ' || TO_CHAR(value2, c_format)
    || ' ' || TO_CHAR(value3, c_format)
    || ' ' || TO_CHAR(value4, c_format)
    || ' ' || TO_CHAR(value5, c_format)
    || ' ' || TO_CHAR(value6, c_format)
  );
END;
/

Which outputs:

   output1    output2    output3    output4    output5    output6
    100.00     200.00     300.00     400.00     500.00     600.00

db<>fiddle here

  • Related