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