Home » SQL & PL/SQL » SQL & PL/SQL » embiding into an object
embiding into an object [message #290068] Thu, 27 December 2007 05:24 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

I have a table say emp_details
It contains empno,ename,deptno.

data in the table

empno ename deptno
1 A 10
1 A 20
1 A 30
1 A 40
2 B 10
now My requirement is to embide all the departments for a perticular employee into one object like

empno ename dept_object
1 A 10,20,30,40
2 B 10

Please help,

Thanks
Re: embiding into an object [message #290073 is a reply to message #290068] Thu, 27 December 2007 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "pivot", there is a nice topic that gives how to do this.

Regards
Michel
Re: embiding into an object [message #290091 is a reply to message #290068] Thu, 27 December 2007 06:36 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks Michel,

But can any one please explain with an example.

Thanks,

Re: embiding into an object [message #290094 is a reply to message #290091] Thu, 27 December 2007 06:44 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://en.wikipedia.org/wiki/Pivot
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:419593546543
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15151874723724
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6923393629227
http://www.orafaq.com/forum/m/286420/43055/

HTH

Regards

Raj

[mod-edit]Corrected URLs.

[Updated on: Thu, 27 December 2007 07:02] by Moderator

Report message to a moderator

Re: embiding into an object [message #290098 is a reply to message #290068] Thu, 27 December 2007 07:00 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi Raj,

Thanks for the quick response.
but , if observe my earlier posting in that
first 4 rows are having similar/same information except the deptno.

required o/P is:

empno ename dept_object
1 A 10,20,30,40
2 B 10


can any one please provide a sample code for this
Re: embiding into an object [message #290099 is a reply to message #290098] Thu, 27 December 2007 07:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ramanajv1968 wrote on Thu, 27 December 2007 14:00

can any one please provide a sample code for this
Sample code is in the last link posted above.

MHE
Re: embiding into an object [message #290108 is a reply to message #290068] Thu, 27 December 2007 08:19 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks Mahir,

But if u see the required output it should be

empno ename dept_object
1 A 10,20,30,40
2 B 10


Note:
The dept_object column should contain the values 10,20,30,40(comma seperated value)
Re: embiding into an object [message #290110 is a reply to message #290108] Thu, 27 December 2007 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What if you try to do it.
All these contain the answer or the way to get it.
Just work a little bit and you will succeed.
If you can't with all these then either you are too lazy or you are not made for this, in both cases change job.

Regards
Michel
Re: embiding into an object [message #290112 is a reply to message #290068] Thu, 27 December 2007 08:45 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Appreciate Michel,

but Please for give me this time.

this is my actual code :
SELECT A.ELMNT_NBR,
ROW_NUMBER() OVER(PARTITION BY A.PLCY_NBR, A.CO_CD,
A.ELMNT_NBR ORDER BY A.PLCY_NBR,
A.CO_CD, A.ELMNT_NBR, A.COMMS_SPLIT_NBR
) AS COMMS_SPLIT_NBR,
A.CLNT_REF_NBR INSURD_CLNT_REF_NBR,
'I' || A.PRIMRY_SECDRY_LIFE_IND INSURD_ROLE_CD,
C.LAST_NAME || ',' || C.FIRST_NAME || ',' ||
C.MIDDLE_NAME || ',' || C.SUFFIX_NAME INSURD_FULL_NAME,
D.ISSUE_AGE,
E.CLNT_REF_NBR AGNT_CLNT_REF_NBR
FROM CLEAR_DAILY_FACT_VW A,
PRDCT_DIM B,
CLNT_DIM C,
COV_DIM D,
AGNT_DIM E
WHERE A.PRDCT_DIM_ID = B.PRDCT_DIM_ID
AND A.CLNT_REF_NBR = C.CLNT_REF_NBR
AND A.COV_DIM_ID = D.COV_DIM_ID
AND A.AGNT_DIM_ID = E.AGNT_DIM_ID
AND A.PLCY_NBR = 'AAA0524326'
ORDER BY ELMNT_NBR,
COMMS_SPLIT_NBR;

O/p of the above query is:
ELMNT_NBR COMMS_SPLIT_NBR INSURD_CLNT_REF_NBR INSURD_ROLE_CD INSURD_FULL_NAME ISSUE_AGE AGNT_CLNT_REF_NBR
1 1 4235445 IP GERDIN,LARRY,J, 64 109
1 2 4235445 IP GERDIN,LARRY,J, 64 3309060
2 1 4235446 IS GERDIN,RANDALYNN,D, 60 109
2 2 4235446 IS GERDIN,RANDALYNN,D, 60 3309060

Required O/P is:

ELMNT_NBR COMMS_SPLIT_NBR INSURD_CLNT_REF_NBR INSURD_ROLE_CD INSURD_FULL_NAME ISSUE_AGE AGNT_CLNT_REF_NBR
1 1,2 4235445 IP GERDIN,LARRY,J, 64 109,3309060
2 1 4235446 IS GERDIN,RANDALYNN,D, 60 109,3309060
Re: embiding into an object [message #290115 is a reply to message #290112] Thu, 27 December 2007 09:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't read non-formated query and output.
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: embiding into an object [message #290126 is a reply to message #290112] Thu, 27 December 2007 10:30 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I was so tempted to spoon feed you but i chose not to. I am going to give you one more link. It exactly explains what you want. Read it, try it, you will definitely get it.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336

Try and try and try until you succeed. Take it as a challenge and come back with your own solution. That's the only way you will learn it. Good Luck and happy learning pivot.

Regards

Raj
Previous Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Next Topic: Forcing Commit
Goto Forum:
  


Current Time: Sun Dec 04 00:29:46 CST 2016

Total time taken to generate the page: 0.07882 seconds