| Column to Row [message #649391] |
Thu, 24 March 2016 01:59  |
 |
reach2nm
Messages: 5
Registered: March 2016
|
Junior Member |
|
|
Hi,
Please help me to get the below result in oracle
1. String 'WELCOME' needs to be display like below using oracle function
W
E
L
C
O
M
E
2. Create table Test(col1 char(1),col2 number);
Table created
insert into test values ('A',2);
insert into test values ('B',3);
select * from test;
Col1 Col2
A 2
B 3
I want to write a query which will display the result as below
Rows will be display based on col2 values.
Col1 Col2
A 2
A 2
B 3
B 3
B 3
3. I want to generate a 10 sequence number from an empty table without using sequence.
create table emp_test(id number,ename varchar2(30));
|
|
|
|
|
|
|
|
| Re: Column to Row [message #649395 is a reply to message #649394] |
Thu, 24 March 2016 02:13  |
John Watson
Messages: 8938
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sorry, I don't understand. Does this
Quote:I have not stuck on any work related to above questions mean that you are not stuck? That you have finished the homework? In rthat case, you can post your solutions as examples that will help other people.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Column to Row [message #649400 is a reply to message #649399] |
Thu, 24 March 2016 04:03 |
John Watson
Messages: 8938
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Take your first homework question:
Quote:1. String 'WELCOME' needs to be display like below using oracle function
W
E
L
C
O
M
E What have you done so far? Nothing?? I would begin by writing a CREATE TABLE statement and then one INSERT statement. That will set up the problem. Then you need to write a SELECT statement. Start with a simple one, and then look up the various functions and constructs that can convert one row into seven.
|
|
|
|
|
|
| Re: Column to Row [message #649403 is a reply to message #649401] |
Thu, 24 March 2016 05:02 |
John Watson
Messages: 8938
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sorry, man. I'm not doing your homework. Do bit of work yourself first, and if you get stuck, explain where.
Frankly, I find your attitude odd. I would not employ you until you show a bit more willingness to work.
|
|
|
|
|
|
| Re: Column to Row [message #649409 is a reply to message #649404] |
Thu, 24 March 2016 10:13 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
We have never and we will never spoon feed someone their homework. If you are stuck on a homework problem and you need a hint, then show everything that you have already done and we will give you hints on how to do it. If we just give you the answer, you will never learn how to program and even worse you will not learn how to search for help.
|
|
|
|
| Re: Column to Row [message #649422 is a reply to message #649409] |
Fri, 25 March 2016 04:18 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Bill B wrote on Thu, 24 March 2016 15:13We have never and we will never spoon feed someone their homework. If you are stuck on a homework problem and you need a hint, then show everything that you have already done and we will give you hints on how to do it. If we just give you the answer, you will never learn how to program and even worse you will not learn how to search for help.
But what you WILL do is end up getting a job for which you are not qualified and are not capable of doing properly. Because of this, you will reduce the overall perceived standard of that position thereby reducing its value, subsequently reducing the pay grade associated with it. This will necessarily reduce the costs of the hiring company in terms of wages but will significantly increase their costs in terms of development time (far outstripping the savings in wages). In addition, the quality of work will be lower and the time spent fixing errors will increase. Data errors will cause failures across the business and that, coupled with the increased costs, will cause the decline of that company, throwing many people out of work and by creating a glut in the supply of labour, further devaluing the job and it's standard rates of pay. All this will cause a reduction in the finances available to the market (fewer people working, less money to spend on consumables, lower demand, leads to reduced supply levels, leads to lower profits which leads to lower wages). This race to the bottom will lead to the total collapse of the world's economy and leave us all scratching a living by selling our few possessions and the occasional pretty stone that we find in our hovel, fighting over scraps and generally being uncivilised.
SO, with all that in mind, rather than causing the collapse of civilisation as we know it, how about you put a bit of effort in and try to write the solution to your problem yourself, in doing so, identify what issues you have and come back and ask questions, we like questions (rather than demands that we write code for you). We will help you understand those issues and we will help you find out how to develop the appropriate code to solve your issue.
Save civilisation reach2nm, do the work 
|
|
|
|
|
|
| Re: Column to Row [message #649450 is a reply to message #649449] |
Sat, 26 March 2016 01:14 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
garan wrote on Sat, 26 March 2016 05:57Hi
we cannot spoon feed with the code as solution but I can tell you the approach for question 1
1. Create a type TYP_SINGLE_CHAR which is table of varchar2(1) basically create a nested table.
2. Create a Function say GET_CHAR_BY_CHAR which accepts P_NAME as string and returns TYPE TYP_SINGLE_CHAR as output
3. In the function logic loop character by character from P_NAME and store it in the collection TYP_SINGLE_CHAR
4. Call the function something like SELECT * FROM TABLE(GET_CHAR_BY_CHAR('ABC')) which will return each character in a separate line as output in sqlplus
Please try and let me know
garani
Massively over complex solution. This can be done in sql.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
