Home » SQL & PL/SQL » SQL & PL/SQL » How to display a name in 100 columns using dual (Oracle10G)
How to display a name in 100 columns using dual [message #442841] Wed, 10 February 2010 23:33 Go to next message
vibhavari
Messages: 19
Registered: October 2009
Junior Member
Hi All,

How to display a name in 100 columns using dual in single sql
?
Re: How to display a name in 100 columns using dual [message #442842 is a reply to message #442841] Wed, 10 February 2010 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How to display a name in 100 columns using dual in single sql?
SQL> DESC FOOBAR

What is input?
What is desired results?

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: How to display a name in 100 columns using dual [message #442843 is a reply to message #442842] Wed, 10 February 2010 23:42 Go to previous messageGo to next message
vibhavari
Messages: 19
Registered: October 2009
Junior Member
SELECT * from v$version gave
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 .

Output->

I need the output like
scott,scott,scott, scott.....100times....
from dual table.

Input-> A select sql .
Operating system is window server

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 Go to previous messageGo to next message
ramoradba
Messages: 2454
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 Wink

sriram Smile

[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 #442851 is a reply to message #442848] Thu, 11 February 2010 00:04 Go to previous messageGo to next message
vibhavari
Messages: 19
Registered: October 2009
Junior Member
Thanks a lot

[Updated on: Thu, 11 February 2010 00:27] by Moderator

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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 #442885 is a reply to message #442851] Thu, 11 February 2010 03:18 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
SELECT LPAD('SCOTT,',500,'SCOTT,') 
FROM DUAL
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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 #442895 is a reply to message #442893] Thu, 11 February 2010 03:39 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Doh! Thanks Michel Smile
Re: How to display a name in 100 columns using dual [message #442900 is a reply to message #442895] Thu, 11 February 2010 03:51 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
BUt for 100 columns
it should be
select substr(LPAD('SCOTT,',600,'SCOTT,'),1,599) from dual;


sriram
Re: How to display a name in 100 columns using dual [message #442901 is a reply to message #442900] Thu, 11 February 2010 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't SUBSTR if you fix the value as I did. Just
SELECT LPAD('SCOTT',599,'SCOTT,') FROM DUAL;

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 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Thu, 11 February 2010 15:25
You 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 Go to previous messageGo to next message
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 #442907 is a reply to message #442905] Thu, 11 February 2010 04:22 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Until of course, you look at his clarification of requirements:

Quote:
I need the output like
scott,scott,scott, scott.....100times....
from dual table.
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 Go to previous messageGo to next message
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 Go to previous message
pablolee
Messages: 2835
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>
Previous Topic: Basic PL/SQL
Next Topic: NVL date return value
Goto Forum:
  


Current Time: Fri Dec 09 03:37:59 CST 2016

Total time taken to generate the page: 0.10148 seconds