Home » SQL & PL/SQL » SQL & PL/SQL » Splitting one column inti two columns
Splitting one column inti two columns [message #257698] Thu, 09 August 2007 00:55 Go to next message
lavanya_vadlavalli
Messages: 4
Registered: August 2007
Junior Member
i Have a column Account_number and i want to split that column into two columns a1 and a2 based on the account_number value
Re: Splitting one column inti two columns [message #257709 is a reply to message #257698] Thu, 09 August 2007 01:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
so what is stopping you from doing so?
Re: Splitting one column inti two columns [message #257720 is a reply to message #257698] Thu, 09 August 2007 01:34 Go to previous messageGo to next message
lavanya_vadlavalli
Messages: 4
Registered: August 2007
Junior Member
select distinct
case when account_number like '110%' then account_number else 0 end a1,
case when account_number like '511%' then account_number else 0 end a2
from jpm_tda_view
group by Account_number


This is the query i wrote for splitting

But i am not getting the output in exact format
it's diasplaying null values

i want the exact way to get correct formatted output
Re: Splitting one column inti two columns [message #257747 is a reply to message #257720] Thu, 09 August 2007 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But i am not getting the output in exact format

Quote:
i want the exact way to get correct formatted output

As you don't tell us which one it should be we can't help.
Quote:
it's diasplaying null values

Maybe you HAVE null values.

Regards
Michel
Re: Splitting one column inti two columns [message #257755 is a reply to message #257747] Thu, 09 August 2007 02:16 Go to previous messageGo to next message
lavanya_vadlavalli
Messages: 4
Registered: August 2007
Junior Member
I need the exact query to split the column with out getting any nulls or zero's
Re: Splitting one column inti two columns [message #257757 is a reply to message #257755] Thu, 09 August 2007 02:21 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
it would be gr8 if u provide some sample data and desire output format

--Yash
Re: Splitting one column inti two columns [message #257767 is a reply to message #257757] Thu, 09 August 2007 02:34 Go to previous messageGo to next message
lavanya_vadlavalli
Messages: 4
Registered: August 2007
Junior Member
The Account_number is to be splitted into two columns called A1, A2

the account_number column contains string data only

the A1 column should contain the accountnumbers startng with 110
and
the A2 colum should contain the account numbers staring with 511

i used the query as

Select
Case when Account_number like '110%' then account_number else 0 end A1,
Case When Account_number like '511%' then account_number else 0 end A2
from Jpm_tda_view
group by account_number


but its giving the output as

 A1                  A2
                   511432
110987
110234
                   511236
                   511232
110654




but for me the output should look as

 A1                     A2
110987                511432
110234                511236
110654                511232


[Mod-edit]applied formatting tags. Read Michel's reply.

[Updated on: Thu, 09 August 2007 03:46] by Moderator

Report message to a moderator

Re: Splitting one column inti two columns [message #257770 is a reply to message #257767] Thu, 09 August 2007 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why "110987 511432" and not "110987 511232"?

Please read and follow How to format your posts

Regards
Michel
Re: Splitting one column inti two columns [message #257805 is a reply to message #257770] Thu, 09 August 2007 03:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You realize that you will lose the meaning of your rows then? If you just shift rows, the different column values have no relation. Your row wise integrity will be lost. In short: bad idea.

MHE
Re: Splitting one column inti two columns [message #257908 is a reply to message #257698] Thu, 09 August 2007 09:36 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
IS this for a report where you want two columns or do you want to create a new column? If you want to only display them, then you can do something like this

select a.account_number a1, b.account_number a2
from 
(select account_number,rownum rnum
 from
 (select account_number
  from Jpm_tda_view
  where account_number like '110%'
  group by account_number
  order by account_number)) a,
(select account_number,rownum rnum
 from
 (select account_number
  from Jpm_tda_view
  where account_number like '551%'
  group by account_number
  order by account_number)) b,
 (select rownum rnum
  from all_objects) c
where c.rnum = a.rnum(+)
and c.rnum = b.rnum(+)
and NOT (a.rnum is null and b.rnum is null);

[Updated on: Thu, 09 August 2007 09:37]

Report message to a moderator

Previous Topic: read txt file and update into db
Next Topic: ora-01466
Goto Forum:
  


Current Time: Wed Dec 07 22:22:23 CST 2016

Total time taken to generate the page: 0.09373 seconds