Home » SQL & PL/SQL » SQL & PL/SQL » Multiple records in one row
Multiple records in one row [message #393017] Fri, 20 March 2009 00:47 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi gurus,

I am having one question in one table.
And all related options in other table.
Now I have to display question and related options in one single record.
I know that this question is already posted in this forum.
But I am not able to search that.
Please tell me that what should I search for?

regards,
Delna

[Updated on: Mon, 23 March 2009 13:12] by Moderator

Report message to a moderator

Re: Multiple records in one row [message #393020 is a reply to message #393017] Fri, 20 March 2009 00:51 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
HUH?
You have a problem.
Please clarify.
Re: Multiple records in one row [message #393022 is a reply to message #393020] Fri, 20 March 2009 00:57 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Helo BlackSwan,

I am not expecting any solution of my question from you.
So please don't reply to my questions.

regards,
Delna
Re: Multiple records in one row [message #393024 is a reply to message #393022] Fri, 20 March 2009 01:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry to disappoint you, but I think with the "problem description" given, it might be challenging to wait for a more usable answer..
Re: Multiple records in one row [message #393025 is a reply to message #393017] Fri, 20 March 2009 01:05 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>So please don't reply to my questions.
Others should comply.
Re: Multiple records in one row [message #393027 is a reply to message #393024] Fri, 20 March 2009 01:06 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks Frank sir,

Actually I want to display multiple records on one line(as one record).
And I know that this type of question is asked before, and I am searching for that. But failed. So what should be the search keyword?

regards,
Delna
Re: Multiple records in one row [message #393028 is a reply to message #393017] Fri, 20 March 2009 01:07 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
delna.sexy wrote on Fri, 20 March 2009 06:47
Hi gurus,

I am having one question in one table.
And all related options in other table.
Now I have to display question and related options in one single record.
I know that this question is already posted in this forum.
But I am not able to search that.
Please tell me that what should I search for?

JOIN for joining the tables
STRAGG, CONCAT_ALL or SYS_CONNECT_BY_PATH for transforming multiple rows into one.
Re: Multiple records in one row [message #393029 is a reply to message #393027] Fri, 20 March 2009 01:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to flyboy's reply, the process is called PIVOTing
Re: Multiple records in one row [message #393036 is a reply to message #393029] Fri, 20 March 2009 01:56 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
One more to add, WM_CONCAT (undocumented).
Re: Multiple records in one row [message #393127 is a reply to message #393017] Fri, 20 March 2009 07:07 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks all.

I got my solution using RANK() and GROUP BY.
  1  SELECT qo.qid_fk,
  2         MAX(DECODE(qo.que_rank, 1, substr(qo.options,1,15), NULL)) AS o1,
  3         MAX(DECODE(qo.que_rank, 2, substr(qo.options,1,15), NULL)) AS o2,
  4         MAX(DECODE(qo.que_rank, 3, substr(qo.options,1,15), NULL)) AS o3,
  5         MAX(DECODE(qo.que_rank, 4, substr(qo.options,1,15), NULL)) AS o4,
  6         MAX(DECODE(qo.que_rank, 5, substr(qo.options,1,15), NULL)) AS o5,
  7         MAX(DECODE(qo.que_rank, 6, substr(qo.options,1,15), NULL)) AS o6
  8  FROM   (SELECT qid_fk,options, RANK() OVER (PARTITION BY qid_fk ORDER BY options) que_rank
  9          FROM   question_option) qo
 10* GROUP BY qo.qid_fk


and the output is
    QID_FK|O1             |O2             |O3             |O4             |O5             |O6
==========|===============|===============|===============|===============|===============|======
         1|App Exam       |Application Exa|Application Exp|Aproved Exam   |               |
         2| :global_sessio| :session_state|:app_session   |:mysession     |               |
         3|Container      |Directory      |Project        |Workspace      |               |
         4|List           |Tab            |Theme          |Validation     |               |
         5|Buttons        |Items          |Regions        |Templates      |               |
         6|Asynchronous Co|Fast Path Drive|Persistent Conn|Synchronous Con|               |
         7|All of the Abov|Charts         |Forms          |Reports        |               |
         8|AJAX           |Javascript     |Skins          |Templates      |               |
         9|Application    |Page           |Request        |Session        |               |
        10|Database Schema|Database Sessio|Multi Threaded |Schemaless User|               |
        12|All of the abov|Encapsulation  |Inheritance    |Polymorphism   |               |
        13|Data binding   |Encapsulation  |Inheritance    |Polymorphism   |               |
        14|Personal Home P|Personal Home P|Practise Home P|Practise Home p|               |
        15|Personal Home P|Personal Home P|Practise Home P|Practise Home p|               |
        16|JDataBase Conne|Java DataBase C|Java DataBase C|Java DataBase C|               |
        17|Remote Machine |Remote Mechine |Remote Method I|Remote Method I|               |
        18|dgi12          |dgi13          |dig11          |dig14          |               |
        19|dig21          |dig22          |dig23          |dig24          |               |
        20|dig31          |dig32          |dig33          |dig34          |               |
        21|dig41          |dig42          |dig43          |dig44          |               |
        22|dig51          |dig52          |dig53          |dig54          |               |
        38|M11            |M12            |M13            |M14            |               |
        39|M21            |M22            |M23            |M24            |               |
        40|M31            |M32            |M33            |M34            |               |
        41|M41            |M42            |M43            |M44            |               |
        42|M51            |M52            |M53            |M54            |               |
        43|M61            |M62            |M63            |M64            |               |
        44|M71            |M72            |M73            |M74            |               |
        45|M81            |M82            |M83            |M84            |               |
        46|M91            |M92            |M93            |M94            |               |
        47|M101           |M102           |M103           |M104           |               |
        48|M111           |M112           |M113           |M114           |               |
        49|M121           |M122           |M123           |M124           |               |
        50|M131           |M132           |M133           |M134           |               |
        51|M141           |M142           |M143           |M144           |               |
        52|M151           |M152           |M153           |M154           |               |
        53|digvijay       |gunjan         |sohan          |vandana        |               |
        56|apr            |dec            |june           |mar            |               |
        57|august         |december       |january        |march          |               |
        58|Anuj sir       |Niraj sir      |Vijaybhai      |snehalbhai     |               |
        59|apple          |banana         |grapes         |pineple        |               |


regards,
Delna

[Updated on: Fri, 20 March 2009 07:08]

Report message to a moderator

Previous Topic: Start autonomous transaction without suspending Main transaction
Next Topic: Finding the time diffrence
Goto Forum:
  


Current Time: Wed Dec 07 03:06:00 CST 2016

Total time taken to generate the page: 0.10339 seconds