Multiple records in one row [message #393017] |
Fri, 20 March 2009 00:47  |
 |
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 #393028 is a reply to message #393017] |
Fri, 20 March 2009 01:07   |
flyboy
Messages: 1903 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 #393127 is a reply to message #393017] |
Fri, 20 March 2009 07:07  |
 |
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
|
|
|