OraFAQ Forum: SQL & PL/SQL » How to Display Multiple rows in a single row but in different columns

Home » SQL & PL/SQL » SQL & PL/SQL » How to Display Multiple rows in a single row but in different columns (Oracle 11.2)

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

How to Display Multiple rows in a single row but in different columns [message #604979]

Mon, 06 January 2014 14:13

deepugun
Messages: 6
Registered: January 2014

Junior Member

Hello All,
Please look at the Attached image and if possible please guide me towards a solution.
Thanks

Attachment: Untitled.jpg
(Size: 48.61KB, Downloaded 553 times)

Report message to a moderator

Re: How to Display Multiple rows in a single row but in different columns [message #604980 is a reply to message #604979]

Mon, 06 January 2014 14:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: How to Display Multiple rows in a single row but in different columns [message #604984 is a reply to message #604980]

Mon, 06 January 2014 14:27

deepugun
Messages: 6
Registered: January 2014

Junior Member

Hello BlackSwan,

I have tried to type in the question as below . But it was loosing its formatting and hence thought that it is better to take a screen shot of the question and post. Sorry for not following the right etiquette
Reposting the question below:
I have a table in which data is stored in the below displayed fashion.

TRANS_ID Ty_1 NU_1
38960 BP 5215153159
38960 EM ramb@T.NET
38960 FX 5115538033
38960 HP 4419965055
38960

For some weird purpose, I would have to display the same data in the below listed fashion
TRANS_ID Ty_1 NU_1 Ty_2 NU_2 Ty_3 NU_3 Ty_4 NU_4 Ty_5 NU_5
38960 BP 5215153159 EM ramb@T.NET FX 5115538033 HP 4419965055

If possible, Please guide me in the right direction.
Thanks

Report message to a moderator

Re: How to Display Multiple rows in a single row but in different columns [message #604985 is a reply to message #604984]

Mon, 06 January 2014 14:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Your example shows 5 rows being pivoted into a single line.
what is the maximum number of rows to be displayed across the returned data; 5, 50, 500, 5000, or more?

Report message to a moderator

Re: How to Display Multiple rows in a single row but in different columns [message #604986 is a reply to message #604985]

Mon, 06 January 2014 15:13

deepugun
Messages: 6
Registered: January 2014

Junior Member

Thanks for the reply. As there are five records for each trans_ID in the table, I would have to display 5 rows into a single row.
Note: They are around 445 records with 89 unique Trans_ID's i.e. each trans_Id is repeated 5 times. I would have to display 89 rows in all i.e. display one row instead of five rows(in the shown format).
Thanks

Report message to a moderator

Re: How to Display Multiple rows in a single row but in different columns [message #605029 is a reply to message #604986]

Tue, 07 January 2014 00:26

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is a standard "pivot" query many times answered here (and everywhere on the web), please do search.

Report message to a moderator

Re: How to Display Multiple rows in a single row but in different columns [message #605036 is a reply to message #604986]

Tue, 07 January 2014 01:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

deepugun wrote on Tue, 07 January 2014 02:43

They are around 445 records with 89 unique Trans_ID's i.e. each trans_Id is repeated 5 times. I would have to display 89 rows in all i.e. display one row instead of five rows(in the shown format).

Using PIVOT you need to hardcode the different combinations of Ty_ and NU_ for 89 times. From Ty_1 and NU_1 to Ty_89 and NU_89

Report message to a moderator

Previous Topic:	Decode explanation
Next Topic:	Can a procedure have multiple OUT sy_refcursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:06:24 CDT 2024