Home » SQL & PL/SQL » SQL & PL/SQL » How to Display Multiple rows in a single row but in different columns (Oracle 11.2)
How to Display Multiple rows in a single row but in different columns [message #604979] Mon, 06 January 2014 14:13 Go to next message
deepugun
Messages: 4
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 33 times)
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 Go to previous messageGo to next message
BlackSwan
Messages: 22499
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
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 Go to previous messageGo to next message
deepugun
Messages: 4
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 22499
Registered: January 2009
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?
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 Go to previous messageGo to next message
deepugun
Messages: 4
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
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.

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 Go to previous message
Lalit Kumar B
Messages: 1820
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
Previous Topic: Decode explanation
Next Topic: Can a procedure have multiple OUT sy_refcursor
Goto Forum:
  


Current Time: Fri Jul 25 09:03:21 CDT 2014

Total time taken to generate the page: 0.14414 seconds