Home » SQL & PL/SQL » SQL & PL/SQL » Row to column conversion
Row to column conversion [message #246443] Thu, 21 June 2007 00:29 Go to next message
arunvijay_1983
Messages: 4
Registered: June 2007
Junior Member
Hi Frenz,

I need to concatenate a column that is present in more 2(or more) rows into a column in a single row.

i will first tel my requirement thro a example.

This is how the data look like.

COL1       COL2 COL3	     COL4	   COL5   COL6
---------- ---  ------------ ------------  ----   ---------
4338307033 013  490082538666 GROWTH	   GROW   5XF013745
4338307033 013  490082538666 INCOME	   INCO   5XF013745
4191315714 013  416004775485 INCOME	   I      NN3917387


i want to concatenate col4 values(and put in a single row)if col1,col2,col3,col6 are same.. i want the output like the following

COL1       COL2 COL3	     COL4          COL5   COL6
---------- ---  ------------ ------------- ----   ---------
4338307033 013  490082538666 GROWTH,INCOME GROW   5XF013745
4191315714 013  416004775485 INCOME        I      NN3917387


Original Record1 and record2 have col1,col2,col3,col6 to be the same. So i want to concatenate col4 values for those records and put as 'Growth,Income' in a single record - (this is new record1). Since Orginal record3 is single, we simply put 'Income'(this is new record2).

Hope i have made myself clear. let me know if i am not clear. Pls help me in this case.

[Updated on: Thu, 21 June 2007 01:21] by Moderator

Report message to a moderator

Re: Row to column conversion [message #246447 is a reply to message #246443] Thu, 21 June 2007 00:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How many of the posting guidelines in the STICKY posts at the top of this forum did you read & FOLLOW

[Updated on: Thu, 21 June 2007 00:42] by Moderator

Report message to a moderator

Re: Row to column conversion [message #246449 is a reply to message #246443] Thu, 21 June 2007 00:46 Go to previous messageGo to next message
arunvijay_1983
Messages: 4
Registered: June 2007
Junior Member
i am a new member to this forum. not sure what guideliness u r pointing to
Re: Row to column conversion [message #246454 is a reply to message #246443] Thu, 21 June 2007 00:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/59966/74940/
for one & next topic below it
Re: Row to column conversion [message #246455 is a reply to message #246449] Thu, 21 June 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The 2 first topics at the top of the forum.

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Row to column conversion [message #246475 is a reply to message #246455] Thu, 21 June 2007 01:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you search for string concatenation you will find several options. Keywords you're looking for are 'CONCAT_ALL', 'STRAGG', 'SYS_CONNECT_BY_PATH'.

A lot depends on the version you're using. So: what's your Oracle version?

MHE
Re: Row to column conversion [message #246478 is a reply to message #246443] Thu, 21 June 2007 01:25 Go to previous messageGo to next message
arunvijay_1983
Messages: 4
Registered: June 2007
Junior Member
Oracle 10G.
Re: Row to column conversion [message #246486 is a reply to message #246478] Thu, 21 June 2007 01:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have a look at this thread.

MHE
Re: Row to column conversion [message #246521 is a reply to message #246443] Thu, 21 June 2007 03:49 Go to previous message
arunvijay_1983
Messages: 4
Registered: June 2007
Junior Member
Got the solution that i require by going thro the links.. Appreciate it
Previous Topic: %
Next Topic: Issue with a query
Goto Forum:
  


Current Time: Fri Dec 09 15:52:53 CST 2016

Total time taken to generate the page: 0.07875 seconds