Home » SQL & PL/SQL » SQL & PL/SQL » Sql query or pl/sql program
Sql query or pl/sql program [message #307585] Wed, 19 March 2008 03:55 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

i have a table with the following data:
Name   Numb

REG    3
TOT    5


i want to display teh output as below

REG           TOT
-----------------------
REG           TOT
REG           TOT
REG           TOT
              TOT
              TOT


How can i do this in SQL or PL/SQL.

Thanks,
Srinivas

[EDITED by LF: fixed [code] tags]

[Updated on: Wed, 19 March 2008 04:03] by Moderator

Report message to a moderator

Re: Sql query or pl/sql program [message #307588 is a reply to message #307585] Wed, 19 March 2008 03:59 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
146 posts and you still can't read a sticky?
This is not an expert question.
Re: Sql query or pl/sql program [message #307590 is a reply to message #307585] Wed, 19 March 2008 04:03 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

here formatted.

i have a table with the following data:
Name Numb

REG 3
TOT 5


i want to display teh output as below


REG TOT
-----------------------
REG TOT
REG TOT
REG TOT
    TOT
    TOT



How can i do this in SQL or PL/SQL.

Thanks,
Srinivas
Re: Sql query or pl/sql program [message #307637 is a reply to message #307585] Wed, 19 March 2008 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
With the limits given in following table definition:
SQL> create table t (name varchar2(10) primary key, nb integer check (nb > 0));

Table created.

SQL> insert into t values ('REG',3);

1 row created.

SQL> insert into t values ('TOT',5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t order by 1;
NAME               NB
---------- ----------
REG                 3
TOT                 5

2 rows selected.

SQL> set head off
SQL> set feed off
SQL> col nop noprint
SQL> col line noprint
SQL> with 
  2    lines as (
  3      select level line from dual connect by level <= (select max(nb) from t)
  4    ),
  5    data as (select name, nb, row_number() over (order by name) rn from t)
  6  select 1 nop, 0 line, 
  7         max(replace(sys_connect_by_path(lpad(name,10),'/'),'/',' ')||'
  8  '||sys_connect_by_path(lpad('-',10,'-'),' '))
  9  from data
 10  connect by prior rn = rn - 1
 11  start with rn = 1
 12  union all 
 13  select 2 nop, line, 
 14         max(replace(sys_connect_by_path(lpad(decode(sign(nb-line),-1,' ',name),
 15                                              10),
 16                                         '/'),
 17                     '/',' '))
 18  from data, lines
 19  connect by prior line = line and prior rn = rn-1
 20  start with rn = 1
 21  group by line
 22  order by 1, 2
 23  /
        REG        TOT
 ---------- ----------
        REG        TOT
        REG        TOT
        REG        TOT
                   TOT
                   TOT

Regards
Michel

[Updated on: Wed, 19 March 2008 06:34]

Report message to a moderator

Re: Sql query or pl/sql program [message #307648 is a reply to message #307585] Wed, 19 March 2008 06:10 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thank You very much
Re: Sql query or pl/sql program [message #307658 is a reply to message #307648] Wed, 19 March 2008 06:34 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't know what might be the use of this?

Regards
Michel

[Updated on: Wed, 19 March 2008 06:34]

Report message to a moderator

Previous Topic: TRIGGER ISSUES
Next Topic: ORA-01722
Goto Forum:
  


Current Time: Mon Feb 17 20:31:50 CST 2025