Sql query or pl/sql program [message #307585] |
Wed, 19 March 2008 03:55  |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi,
i have a table with the following data:
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 #307590 is a reply to message #307585] |
Wed, 19 March 2008 04:03   |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi,
here formatted.
i have a table with the following data:
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   |
 |
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
|
|
|
|
|