To print the column value and a string [message #390674] |
Mon, 09 March 2009 04:21  |
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 #391123 is a reply to message #390723] |
Wed, 11 March 2009 02:36   |
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 #391128 is a reply to message #391123] |
Wed, 11 March 2009 03:20   |
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 #391155 is a reply to message #391153] |
Wed, 11 March 2009 04:33   |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #391260 is a reply to message #391258] |
Wed, 11 March 2009 10:03  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|