Home » SQL & PL/SQL » SQL & PL/SQL » To print the column value and a string (Oracle -10G)
To print the column value and a string [message #390674] Mon, 09 March 2009 04:21 Go to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I have a table with Data like Oracle,sqlserver,access.
I have to print the data like

Oracle
hello
sqlserver
hello
access
hello

Create table test(prod varchar2(100));
Insert into test values('oracle');
Insert into test values('sqlserver');
Insert into test values('access');



How this can be done using oracle query.

Thanks in advance
Re: To print the column value and a string [message #390685 is a reply to message #390674] Mon, 09 March 2009 04:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you need to display it like that, or do you need the actual records?
And if the answer is display-only, what tool do you want to use for displaying?
Re: To print the column value and a string [message #390693 is a reply to message #390674] Mon, 09 March 2009 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Select 'hello
'||prod
from test
/

Regards
Michel
Re: To print the column value and a string [message #390723 is a reply to message #390674] Mon, 09 March 2009 07:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Might be

 SELECT prod||CHR(10)||'hello'  FROM TEST


Smile
Raj.
Re: To print the column value and a string [message #391123 is a reply to message #390723] Wed, 11 March 2009 02:36 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
In the table i have data like oracle,sqlserver,access.
while printing output it should print like this
hello is the string which should get displayed in between each
database value

oracle
hello
sqlserver
hello
access
hello

Kindly help me.

Thanks in advance
Re: To print the column value and a string [message #391124 is a reply to message #391123] Wed, 11 March 2009 02:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are kidding, right?
Re: To print the column value and a string [message #391128 is a reply to message #391123] Wed, 11 March 2009 03:20 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@convey05,

Did you try Michel's or Rajavu's query.
That will generate the output as
PROD||'HELLO'
--------------------------------------------------------------------------------
oracle
hello

sqlserver
hello

access
hello


3 rows selected.



OR Do you need the output as
PROD
--------------------------------------------------------------------------------
oracle
HELLO
sqlserver
HELLO
access
HELLO

6 rows selected.



Although I am stumped by why you need such an Output, are you trying to get the word 'HELLO' as a seperate record? Notice the difference in the number of records in both the cases. I think Frank has already asked you this question
Quote:

Do you need to display it like that, or do you need the actual records?
And if the answer is display-only, what tool do you want to use for displaying?


Please answer them first.

Regards,
Jo
Re: To print the column value and a string [message #391153 is a reply to message #391128] Wed, 11 March 2009 04:19 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
It should display 6 rows.

Thanks
Re: To print the column value and a string [message #391155 is a reply to message #391153] Wed, 11 March 2009 04:33 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@convey05,

Hope the following piece of code helps:
SQL> SELECT   prod
  2      FROM (SELECT ROWNUM rn, prod
  3              FROM test_tab
  4            UNION
  5            SELECT     (LEVEL + LEVEL / POWER (10, LENGTH (TO_CHAR (LEVEL)))
  6                       ) rn,
  7                       'HELLO'
  8                  FROM DUAL
  9            CONNECT BY LEVEL <= (SELECT COUNT (*)
 10                                   FROM test_tab)) a1
 11  ORDER BY a1.rn;

PROD
--------------------------------------------------------------------------------
oracle
HELLO
sqlserver
HELLO
access
HELLO

6 rows selected.

SQL> 

Experts may give you a better solution or logic.

Regards,
Jo
Re: To print the column value and a string [message #391163 is a reply to message #391153] Wed, 11 March 2009 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select line, decode(mod(line,2),1,prod,'hello') val
  2  from test, 
  3       (select 1 line from dual union all select 2 from dual)
  4  order by prod, line
  5  /
      LINE VAL
---------- ------------------------------------------------------
         1 access
         2 hello
         1 oracle
         2 hello
         1 sqlserver
         2 hello

6 rows selected.

Regards
Michel
Re: To print the column value and a string [message #391220 is a reply to message #391163] Wed, 11 March 2009 08:28 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Can always depend on the experts here to get a better and simple logic...

Thanks Michel.... http://http://www.mysmiley.net/imgs/smile/animated/anim_37.gif

Regards,
Jo
Re: To print the column value and a string [message #391258 is a reply to message #391220] Wed, 11 March 2009 09:58 Go to previous messageGo to next message
rsampathy
Messages: 2
Registered: July 2006
Junior Member

select replace('Oracle,sqlserver,access',',',CHR(10)) from dual;

Input: Oracle,sqlserver,access

Output:
Oracle
sqlserver
access
Re: To print the column value and a string [message #391260 is a reply to message #391258] Wed, 11 March 2009 10:03 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has nothing to do with what is asked.
Please read the full topic before posting.
Anyway thanks for contributing.

Before your next post, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 11 March 2009 10:04]

Report message to a moderator

Previous Topic: Error inserting special characters to Oracle (merged)
Next Topic: Time - Datatypes question
Goto Forum:
  


Current Time: Mon Dec 05 14:44:23 CST 2016

Total time taken to generate the page: 0.17316 seconds