Home » SQL & PL/SQL » SQL & PL/SQL » How to split col into two columns according to data types
How to split col into two columns according to data types [message #661033] Mon, 06 March 2017 00:36 Go to next message
ssyr
Messages: 50
Registered: January 2017
Member
Hi All,

I have 2 tables containing mix data type values store in in one column. I have to display its name & type according to first table 'ID_Code"

Table 'A'

ID_Code	 type
aaa	aid
111	pgm
bbb	aid
222	pgm
ccc	aid
333	pgm
ddd	aid

Maste Table
Table 'B'
Name	ID
aaa	111
bbb	222
ccc	333
ddd	444

On the basis of ID_CODe
Iwant to separate it into Name & ID column.ALso I have to join these two talbes as well.
How can i display it?
please guid me?

[mod-edit: code tags added by bb]

[Updated on: Mon, 06 March 2017 14:14] by Moderator

Report message to a moderator

Re: How to split col into two columns according to data types [message #661035 is a reply to message #661033] Mon, 06 March 2017 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

RE: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661037 is a reply to message #661033] Mon, 06 March 2017 01:24 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member

Sorry for my above post.


But I am not able to give you exact test cases .Our company does not allowed this. That why I have prepared my own data set.
Could you just provide me just guide line for my able problem?

[Updated on: Mon, 06 March 2017 02:54] by Moderator

Report message to a moderator

Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661038 is a reply to message #661037] Mon, 06 March 2017 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What are you talking about? You are not part of this topic.

Re: How to split col into two columns according to data types [message #661039 is a reply to message #661038] Mon, 06 March 2017 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK I moved your post into the correct topic.

Now, What you posted (your own set of data) IS what you have to convert into a test case that is provide the statements for us to work on your issue.
Give the result you want for the tables and data you gave, and explain this result.
Once again the first step is to provide CREATE TABLE and INSERT statements.


[Updated on: Mon, 06 March 2017 02:52]

Report message to a moderator

Re: How to split col into two columns according to data types [message #661040 is a reply to message #661039] Mon, 06 March 2017 01:57 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Thanks .

Yes.Correct.IT is my own set of data.
Next time onwards I will provide all the cases.

[Edit MC: fix post title]

[Updated on: Mon, 06 March 2017 02:52] by Moderator

Report message to a moderator

Re: How to split col into two columns according to data types [message #661041 is a reply to message #661040] Mon, 06 March 2017 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you no more want an answer to this issue.

[Updated on: Mon, 06 March 2017 02:53]

Report message to a moderator

Re: How to split col into two columns according to data types [message #661042 is a reply to message #661041] Mon, 06 March 2017 02:10 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Not like...
I want the answer for the post.

[Edit MC: fix post title]

[Updated on: Mon, 06 March 2017 02:53] by Moderator

Report message to a moderator

Re: How to split col into two columns according to data types [message #661043 is a reply to message #661042] Mon, 06 March 2017 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So post what is asked.
You even don't know your Oracle version (from V$VERSION).

[Updated on: Mon, 06 March 2017 02:53]

Report message to a moderator

Re: How to split col into two columns according to data types [message #661046 is a reply to message #661043] Mon, 06 March 2017 02:59 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Oracle11g
Re: How to split col into two columns according to data types [message #661047 is a reply to message #661046] Mon, 06 March 2017 03:06 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
There are numerous sub-versions of 11g. You need to be more specific
Re: How to split col into two columns according to data types [message #661048 is a reply to message #661047] Mon, 06 March 2017 03:09 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Re: How to split col into two columns according to data types [message #661049 is a reply to message #661048] Mon, 06 March 2017 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, this is one point, now the other ones.

Re: How to split col into two columns according to data types [message #661052 is a reply to message #661049] Mon, 06 March 2017 03:46 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
what other information do you want ...
Can please tell me?
whatever my issue I have written in the post.
Re: How to split col into two columns according to data types [message #661053 is a reply to message #661052] Mon, 06 March 2017 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What more can I tell more than in my previous posts?
Do you really think you have posted all what I asked?

Re: How to split col into two columns according to data types [message #661054 is a reply to message #661053] Mon, 06 March 2017 03:53 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
We don't know what the relationship between the tables is. We don't know what output you expect.
And we don't have a test case to allow us to easily test solutions to the problem you haven't explained.
Re: How to split col into two columns according to data types [message #661056 is a reply to message #661054] Mon, 06 March 2017 04:03 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
this is the issue...that I have to join the tables but there is no column common in betten them but data is is common.that also be in one table it is store in combined format.& in othert able it is in two separate column.
Re: How to split col into two columns according to data types [message #661057 is a reply to message #661056] Mon, 06 March 2017 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No one can be so dumb that he does not understand what we say, I think you are just a troll.

Re: How to split col into two columns according to data types [message #661058 is a reply to message #661057] Mon, 06 March 2017 07:27 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
Your request is difficult to understand but I will attempt to give you something to try

select a.id_code,a.aid,b.name
from table_a a, table_b b
where a.id_code = b.id;
Re: How to split col into two columns according to data types [message #661061 is a reply to message #661056] Mon, 06 March 2017 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
ssyr wrote on Mon, 06 March 2017 10:03
this is the issue...that I have to join the tables but there is no column common in betten them but data is is common.that also be in one table it is store in combined format.& in othert able it is in two separate column.
If you don't understand the relationship between the tables then you should be asking the person who designed them (or if they're no longer around someone with in-depth knowledge of the system design). We can't guess what it is.
If you tell us the relationship and what output you want then we can tell you want code you need to do the job, but we can't tell you want your own requirements are.
Re: How to split col into two columns according to data types [message #661082 is a reply to message #661061] Mon, 06 March 2017 23:55 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
thanks for all.

Yes it is correct that there is no relation ship between two tables only data is common .
I have to ask to my seniors which table I have to use for this?
Re: How to split col into two columns according to data types [message #661087 is a reply to message #661082] Tue, 07 March 2017 03:02 Go to previous message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes. They should explain the complete requirements to you. That would include a detailed explanation of how to join the two tables together and what the output of the query should be.
Previous Topic: Date closest to event date
Next Topic: Enable novalidate constraint
Goto Forum:
  


Current Time: Sat Oct 20 18:54:28 CDT 2018