Home » SQL & PL/SQL » SQL & PL/SQL » Cut string and place in a separate Column (SQL)
Cut string and place in a separate Column [message #345040] Tue, 02 September 2008 03:40 Go to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
Purpose : was to group "main-person" with related partners-and managed to group the similar records but in ONE column-contacenated together with "|"
- I need to split the name of partners into separate columns (.i.e. every name to be displayed in a single column)

This is the query I have
---------------------------------------------------------
SELECT p1.project_name, p1.serial_no, p1.ben_code, p1.GOVT_CODE, p1.PRG_CODE, p1.PRJ_CODE
,MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(p1.Partner_Name),'|'),2))
KEEP(DENSE_RANK LAST ORDER BY p1.curr) as_string
FROM (SELECT P2.project_name
,P2.Partner_Name
, p2.serial_no, p2.ben_code, p2.GOVT_CODE, p2.PRG_CODE, p2.PRJ_CODE
,ROW_NUMBER()
OVER (PARTITION BY P2.project_name
ORDER BY P2.Partner_Name) curr
,ROW_NUMBER()
OVER (PARTITION BY P2.project_name
ORDER BY P2.Partner_Name) - 1 prev
FROM
ben_sedo_3 P2 ) P1
GROUP BY p1.project_name, p1.serial_no, p1.ben_code, p1.GOVT_CODE, p1.PRG_CODE, p1.PRJ_CODE
START WITH p1.curr = 1
CONNECT BY p1.prev = PRIOR p1.curr
AND p1.project_name = PRIOR p1.project_name
----------------------------------------
The data displayed is as follows :
-------------------------------------------------------------
Project name Partner-name
--------------------------------------------------------------
Lighting Company Nancy Nessim | Laila Negm | Sally Badr
Compass Company Hussein Magdy | May Magdy


-------------------------------------------------------
Need to Display the Data as follows :
----------------------------------------------------------------
Project name Partner-name1 Partner-name2 Partner-name3
--------------------------------------------------------------
Lighting Company Nancy Nessim Laila Negm Sally Badr
Compass Company Hussein Magdy May Magdy


--------------------------------
Your efforts for assistance is highly appreciated

Many thanks,
Rania Salama
Re: Cut string and place in a separate Column [message #345046 is a reply to message #345040] Tue, 02 September 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Your efforts for assistance is highly appreciated

Yours too, 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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Cut string and place in a separate Column [message #345048 is a reply to message #345040] Tue, 02 September 2008 03:48 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please format your post.

Search pivot.

Regards,
Rajat
Re: Cut string and place in a separate Column [message #345060 is a reply to message #345048] Tue, 02 September 2008 04:05 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
Thanks Rajat for your quick reply.

But I already read the pivit/unpivot article that is place in orafaq previous topics- it does not work in our environment because we are not on 10g env.
Re: Cut string and place in a separate Column [message #345065 is a reply to message #345060] Tue, 02 September 2008 04:15 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

But I already read the pivit/unpivot article that is place in orafaq previous topics- it does not work in our environment because we are not on 10g env.



What pivot has to do with 10g env??

Regards,
Rajat
Re: Cut string and place in a separate Column [message #345079 is a reply to message #345065] Tue, 02 September 2008 04:47 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
PIVOT is a SQL operation, introduced in Oracle 11g, that lets you write cross tabulation (also called transposed, crosstab and matrix) queries that rotate rows into columns while aggregating data in the rotation process.
source : http://www.orafaq.com/wiki/PIVOT
Re: Cut string and place in a separate Column [message #345082 is a reply to message #345079] Tue, 02 September 2008 04:53 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I know that this operation is not available in 10g
But
I have not said that you can't achieve this results in 10g.

you have to make your own query to achieve this.

and that's why i have said serach pivot query.

how to make pivot query in oracle 10g.

look at this

http://www.orafaq.com/forum/m/326669/121416/#msg_326669

Regards,
Rajat

[Updated on: Tue, 02 September 2008 04:58]

Report message to a moderator

Re: Cut string and place in a separate Column [message #345090 is a reply to message #345040] Tue, 02 September 2008 05:05 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
raniams wrote on Tue, 02 September 2008 11:40
Purpose : was to group "main-person" with related partners-and managed to group the similar records but in ONE column-contacenated together with "|"
- I need to split the name of partners into separate columns (.i.e. every name to be displayed in a single column)

This is the query I have
---------------------------------------------------------
SELECT p1.project_name, p1.serial_no, p1.ben_code, p1.GOVT_CODE, p1.PRG_CODE, p1.PRJ_CODE
,MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(p1.Partner_Name),'|'),2))
KEEP(DENSE_RANK LAST ORDER BY p1.curr) as_string
FROM   (SELECT P2.project_name
              ,P2.Partner_Name
              , p2.serial_no, p2.ben_code, p2.GOVT_CODE, p2.PRG_CODE, p2.PRJ_CODE
              ,ROW_NUMBER()
              OVER (PARTITION BY P2.project_name
                   ORDER BY     P2.Partner_Name)      curr
              ,ROW_NUMBER()
               OVER (PARTITION BY P2.project_name
               ORDER BY     P2.Partner_Name) - 1  prev
               FROM   
               ben_sedo_3 P2 ) P1
               GROUP BY   p1.project_name, p1.serial_no, p1.ben_code, p1.GOVT_CODE, p1.PRG_CODE, p1.PRJ_CODE
               START WITH p1.curr = 1
               CONNECT BY p1.prev = PRIOR p1.curr
              AND p1.project_name   = PRIOR p1.project_name

----------------------------------------
The data displayed is as follows :
-------------------------------------------------------------
Project name Partner-name
--------------------------------------------------------------
Lighting Company Nancy Nessim | Laila Negm | Sally Badr
Compass Company Hussein Magdy | May Magdy


-------------------------------------------------------
Need to Display the Data as follows :
----------------------------------------------------------------
Project name Partner-name1 Partner-name2 Partner-name3
--------------------------------------------------------------
Lighting Company Nancy Nessim Laila Negm Sally Badr
Compass Company Hussein Magdy May Magdy


--------------------------------
Your efforts for assistance is highly appreciated

Many thanks,
Rania Salama


Re: Cut string and place in a separate Column [message #345093 is a reply to message #345079] Tue, 02 September 2008 05:17 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
raniams wrote on Tue, 02 September 2008 11:47
PIVOT is a SQL operation, introduced in Oracle 11g, that lets you write cross tabulation (also called transposed, crosstab and matrix) queries that rotate rows into columns while aggregating data in the rotation process.
source : http://www.orafaq.com/wiki/PIVOT

The same source:
Quote:
For Oracle versions prior to 11g, the same results can be obtained with this (somewhat bulkier) query: <code of sample query>

By the way, where did you mention Oracle version in your first post?
Re: Cut string and place in a separate Column [message #345365 is a reply to message #345082] Wed, 03 September 2008 04:06 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
rajatratewal wrote on Tue, 02 September 2008 12:53
I know that this operation is not available in 10g
But
I have not said that you can't achieve this results in 10g.

you have to make your own query to achieve this.

and that's why i have said search pivot query.

how to make pivot query in oracle 10g.

look at this

http://www.orafaq.com/forum/m/326669/121416/#msg_326669


I'm thankful for your quick assistance, but please note the following:
-The link you send is not related to my reqyest
- The pivot query is used for "sum"- which is not my case, since i need characters to be displayed in columns
- our environment is Oracle version 9i

Please help me to cut the data that is displayed in column, to be distributed over unique columns.

Many thanks
Re: Cut string and place in a separate Column [message #345370 is a reply to message #345365] Wed, 03 September 2008 04:35 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You can modify the query for 9i.This is same for 10g.

But what i think is that you need the data in different columns.

Pivot query gives data in a single column seperated by space.

Single Column:-
Partner1 Partner2 Partner3
A        B        C


This is one column.
But if you need data in seperate column then you can go for
max(decode.

But i am afraid that will be feasible in your case.

I think the number of partners is not fixed.

Regards,
Rajat
Re: Cut string and place in a separate Column [message #345373 is a reply to message #345370] Wed, 03 September 2008 04:52 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
rajatratewal wrote on Wed, 03 September 2008 12:35
You can modify the query for 9i.This is same for 10g.

But what i think is that you need the data in different columns.

Pivot query gives data in a single column seperated by space.

Single Column:-
Partner1 Partner2 Partner3
A        B        C


This is one column.
But if you need data in seperate column then you can go for
max(decode.

But i am afraid that will be feasible in your case.

I think the number of partners is not fixed.




the max No. of partners would be FOUR
But not necessarily all for every project you get 4 partners, could be sometimes one, or only two , or three

Could you please provide me with sample code?
Many thanks
Re: Cut string and place in a separate Column [message #345383 is a reply to message #345373] Wed, 03 September 2008 05:17 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Check the link i have provided to you.

See the first post.It shows use of Max(Decode

Regards,
Rajat
Re: Cut string and place in a separate Column [message #345552 is a reply to message #345383] Thu, 04 September 2008 02:46 Go to previous message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
I wanted to share the code with you...
select 
substr(Partners,1,decode(instr(Partners,',',1,1),0,length(Partners),instr(Partners,',',1,1)-1)) BEN_NAME2,

substr(
        Partners,
        instr(partners,',',1,1)+1,
        decode(
                instr(partners,',',1,2)
                ,0,
                length(partners)-length(substr(Partners,1,decode(instr(Partners,',',1,1),0,length(Partners),instr(Partners,',',1,1)-1))),
                instr(partners,',',1,2)-instr(partners,',',1,1)-1
              )
      ) BEN_NAME3,


substr(
        Partners,
        instr(partners,',',1,2)+1,
        decode(
                instr(partners,',',1,3)
                ,0,
                length(partners)-length(substr(Partners,1,decode(instr(Partners,',',1,2),0,length(Partners),instr(Partners,',',1,2)-1))),
                instr(partners,',',1,3)-instr(partners,',',1,2)-1
              )
      ) BEN_NAME4,
      
substr(
        Partners,
        instr(partners,',',1,3)+1,
        decode(
                instr(partners,',',1,4)
                ,0,
                length(partners)-length(substr(Partners,1,decode(instr(Partners,',',1,3),0,length(Partners),instr(Partners,',',1,3)-1))),
                instr(partners,',',1,4)-instr(partners,',',1,3)-1
              )
      ) BEN_NAME5
      

from ben_sedo_sedo_3



Thanks for all your feedback.....
Previous Topic: Indexes
Next Topic: how to fetch data from multiple databases
Goto Forum:
  


Current Time: Fri Dec 09 13:50:32 CST 2016

Total time taken to generate the page: 0.09862 seconds