OraFAQ Forum: SQL & PL/SQL » Row to column conversion

Home » SQL & PL/SQL » SQL & PL/SQL » Row to column conversion

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row to column conversion [message #246443]

Thu, 21 June 2007 00:29

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

BlackSwan
Messages: 26766
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

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

Report message to a moderator

Re: Row to column conversion [message #246454 is a reply to message #246443]

Thu, 21 June 2007 00:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/59966/74940/
for one & next topic below it

Report message to a moderator

Re: Row to column conversion [message #246455 is a reply to message #246449]

Thu, 21 June 2007 00:55

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Row to column conversion [message #246475 is a reply to message #246455]

Thu, 21 June 2007 01:24

Maaher
Messages: 7065
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

Report message to a moderator