Home » SQL & PL/SQL » SQL & PL/SQL » How to display a name in 100 columns using dual (Oracle10G)
|
|
|
Re: How to display a name in 100 columns using dual [message #442848 is a reply to message #442843] |
Wed, 10 February 2010 23:59   |
 |
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
IND> SELECT WM_CONCAT(RAM)
2 FROM ( SELECT 'scott' RAM,ROWNUM FROM DUAL CONNECT BY LEVEL <= 10 )
3 /
WM_CONCAT(RAM)
--------------------------------------------------------------------------------
scott,scott,scott,scott,scott,scott,scott,scott,scott,scott
1 row selected.
I want only 10 times 
sriram
[Updated on: Wed, 10 February 2010 23:59] Report message to a moderator
|
|
|
|
Re: How to display a name in 100 columns using dual [message #442856 is a reply to message #442841] |
Thu, 11 February 2010 00:30   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> with
2 data as (
3 select level nb, 'scott' val from dual connect by level <= 10
4 )
5 select trim(',' from max(sys_connect_by_path(val,','))) val
6 from data
7 where nb = 10
8 connect by prior nb = nb-1
9 start with nb = 1
10 /
VAL
--------------------------------------------------------------
scott,scott,scott,scott,scott,scott,scott,scott,scott,scott
Regards
Michel
|
|
|
|
Re: How to display a name in 100 columns using dual [message #442893 is a reply to message #442885] |
Thu, 11 February 2010 03:38   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Nice, a small fix:
SQL> SELECT LPAD('SCOTT',59,'SCOTT,') FROM DUAL;
LPAD('SCOTT',59,'SCOTT,')
-----------------------------------------------------------
SCOTT,SCOTT,SCOTT,SCOTT,SCOTT,SCOTT,SCOTT,SCOTT,SCOTT,SCOTT
1 row selected.
Regards
Michel
|
|
|
|
|
|
Re: How to display a name in 100 columns using dual [message #442902 is a reply to message #442901] |
Thu, 11 February 2010 04:05   |
 |
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 11 February 2010 15:25You don't SUBSTR if you fix the value as I did. Just
SELECT LPAD('SCOTT',599,'SCOTT,') FROM DUAL;
Regards
Michel
Yes...Mine is a reply to pablolee solution.
IND> select LPAD('SCOTT,',599,'SCOTT,') from dual;
LPAD('SCOTT,',599,'SCOTT,')
----------------------------------------------------------------------------------------------------
,.......the last column would be like this.....SCOTTSCOTT,
1 row selected.
Which is wrong.
But if it 'SCOTT', you are right.
Difference in 'SCOTT,' and 'SCOTT', will change the pattern.
Thanks
sriram
|
|
|
Re: How to display a name in 100 columns using dual [message #442905 is a reply to message #442902] |
Thu, 11 February 2010 04:18   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Sorry to be a pedant again, but all of these are displaying the name Scott one hundered times in one column, whereas the OP asked for:Quote:How to display a name in 100 columns using dual in single sql
Something like this:SQL> create or replace type name_10_obj as object
2 (name1 varchar2(30)
3 ,name2 varchar2(30)
4 ,name3 varchar2(30)
5 ,name4 varchar2(30)
6 ,name5 varchar2(30)
7 ,name6 varchar2(30)
8 ,name7 varchar2(30)
9 ,name8 varchar2(30)
10 ,name9 varchar2(30)
11 ,name10 varchar2(30));
12 /
Type created.
SQL>
SQL> create or replace type name_10_tab as table of name_10_obj;
2 /
Type created.
SQL>
SQL> create or replace function make_name_tab (p_name in varchar2) return name_10_tab as
2 t_return name_10_tab := name_10_tab();
3 begin
4 t_return.extend(1);
5 t_return(t_return.last) := name_10_obj(p_name,p_name,p_name,p_name,p_name,p_name,p_name,p_name,p_name,p_name);
6 return t_return;
7 end make_name_tab;
8 /
Function created.
SQL>
SQL> select *
2 from table(make_name_tab('SCOTT'));
NAME1 NAME2 NAME3 NAME4 NAME5 NAME6 NAME7 NAME8 NAME9 NAME10
-------- -------- -------- -------- -------- -------- -------- -------- -------- ---------
SCOTT SCOTT SCOTT SCOTT SCOTT SCOTT SCOTT SCOTT SCOTT SCOTT
SQL>
|
|
|
|
Re: How to display a name in 100 columns using dual [message #442909 is a reply to message #442907] |
Thu, 11 February 2010 04:34   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quite true - it's not what you'd call an accurate specification of the problem.
SQL> set colsep ','
SQL> /
NAME1 ,NAME2 ,NAME3 ,NAME4 ,NAME5 ,NAME6 ,NAME7 ,NAME8 ,NAME9 ,NAME10
-------,-------,-------,-------,-------,-------,-------,-------,-------,--------
SCOTT ,SCOTT ,SCOTT ,SCOTT ,SCOTT ,SCOTT ,SCOTT ,SCOTT ,SCOTT ,SCOTT
|
|
|
Re: How to display a name in 100 columns using dual [message #442910 is a reply to message #442909] |
Thu, 11 February 2010 04:39  |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Quote:Quite true - it's not what you'd call an accurate specification of the problem.
L, reminds me of a 'set of requirements' I recieved for a report requirement last week:
"Hi,
We need some of the finance data broken down by sector. Can you do that?
Thanks"
I paraphrase, but that was the detail level <slaps forehead>
|
|
|
Goto Forum:
Current Time: Sat Feb 15 12:07:54 CST 2025
|