Re: How to show a select in one row

On Tue, 7 Sep 1999 12:55:14 +0200, "Jorge" <pumuky22_at_usa.net> wrote:

>something like this:
>select * from toto
>id
>---
>1
>2
>3
>
>select * from toto
>id
>---
>123
>

I don't think that there is an easy way to do this. One way to accomplish this task would be to create a temporary table with number of columns equal to the number of rows in your table . In Oracle 8i PL/SQL could look like this :

--I have table test with a single column name. The resulting table --temp1 has one row that contains all rows from table test.

declare
  RecCount number;
  execstr varchar2(200) := 'create table temp1 (';   execstr1 varchar2(200) := 'insert into temp1 values(';   cursor mycur is select name from test;   myvar number(5,0);
begin

• make sure that this temporary table doesn't already exists begin execute immediate 'drop table temp1'; end; exception when others then null;
  • count the records in our table open mycur; fetch mycur into myvar; -- count records execstr1 := execstr1 || myvar; LOOP fetch mycur into myvar; exit when mycur%NOTFOUND; execstr1 := execstr1 || ', ' || myvar; end LOOP;

  RecCount := mycur%rowcount;
  close mycur;
  for i in 1..RecCount-1 loop
    execstr := execstr || 'col' || i || ' number, ';   end loop;
  execstr := execstr || 'col' || RecCount || ' number)';

• create a temp table with number of columns = number of rows in --our table execute immediate execstr;
• insert our rows into columns of temp1 table execstr1 := execstr1 || ')';

  execute immediate execstr1;

• now you can query table temp1 end;

Or you could use awk if you run Unix. Received on Tue Sep 07 1999 - 15:14:22 CDT