Home » SQL & PL/SQL » SQL & PL/SQL » merge rows (10.2.1.0)
merge rows [message #571290] Fri, 23 November 2012 01:02 Go to next message
pratik4891
Messages: 53
Registered: February 2011
Member
Please help me to write the query ..
Below are the codes

create table testing1
(
id number,
data varchar2(20)
 )


insert all into testing1 values (1,'100')
            into testing1 values (2,'Ram')
            into testing1 values (3,'Pune')
            into testing1 values (1,'200')
            into testing1 values (2,'Shyam')
            into testing1 values (3,'Mumbai')
            into testing1 values (1,'300')
            into testing1 values (2,'Kamal')
            into testing1 values (3,'Bangalore')
            select * from dual


I want the output as

100 Ram Pune
200 Shyam Mumbai
300 Kamal bangalore


Re: merge rows [message #571292 is a reply to message #571290] Fri, 23 November 2012 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Based on which rules?

Regards
Michel
Re: merge rows [message #571303 is a reply to message #571292] Fri, 23 November 2012 03:13 Go to previous messageGo to next message
pratik4891
Messages: 53
Registered: February 2011
Member
1st row data value will go to output col1
2nd row data value will go to output col2
3rd row data value will go to output col3

and it will repeat the same from the 4th row again

Re: merge rows [message #571304 is a reply to message #571303] Fri, 23 November 2012 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no first, second or third row in a table unless you define an order (from the data).
A table is a heap of rows.

Regards
Michel
Re: merge rows [message #571306 is a reply to message #571304] Fri, 23 November 2012 03:27 Go to previous messageGo to next message
pratik4891
Messages: 53
Registered: February 2011
Member
ok...
when the id column value is 1 the data column value will be col1
when the id column value is 2 the data column value will be col2
when the id column value is 3 the data column value will be col3

I have tried with this but no luck

select decode (id ,1,data) empid
            ,decode (id ,2,data) name
            ,decode (id ,3,data) loc
            from testing1
Re: merge rows [message #571309 is a reply to message #571306] Fri, 23 November 2012 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have tried with this but no luck


Of course, there are several rows with id 1 or 2 or 3.
You must have another column allowing identifying which ones are associated with which ones (and provide a new test case accordingly).

In addition, a question for you to answer: what should be happen (if it is possible) if some final lines should have 2 or 4 or 5 elements?

Regards
Michel

[Updated on: Fri, 23 November 2012 03:51]

Report message to a moderator

Re: merge rows [message #571450 is a reply to message #571309] Mon, 26 November 2012 02:01 Go to previous messageGo to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Pratik ,



SQL> 
SQL> with data as
  2   (select rownum sr, data id, null name, null loc
  3      from testing1 t1
  4     where t1.id = 1
  5    union
  6    select rownum, null id, data name, null loc
  7      from testing1 t1
  8     where t1.id = 2
  9    union
 10    select rownum, null id, null name, data loc
 11      from testing1 t1
 12     where t1.id = 3)
 13  select max (id) id, max(name) name, max(loc) loc from data group by sr
 14  ;
 
ID                   NAME                 LOC
-------------------- -------------------- --------------------
100                  Ram                  Pune
200                  Shyam                Mumbai
300                  Kamal                Bangalore
 
SQL> 



Let me know the above query was giving the desired out put

Thanks
SaiPradyumn
Re: merge rows [message #571451 is a reply to message #571290] Mon, 26 November 2012 02:09 Go to previous messageGo to next message
tony123
Messages: 9
Registered: August 2012
Junior Member

if u can make sure correct order.

SQL> 
SQL> SELECT A, B, C
  2    FROM (SELECT ID,
  3                 DATA A,
  4                 LEAD(DATA) OVER(ORDER BY NULL) B,
  5                 LEAD(DATA, 2) OVER(ORDER BY NULL) C
  6            FROM TESTING1)
  7   WHERE ID = 1;
 
A                    B                    C
-------------------- -------------------- --------------------
100                  Ram                  Pune
200                  Shyam                Mumbai
300                  Kamal                Bangalore
 
SQL> 
Re: merge rows [message #571456 is a reply to message #571451] Mon, 26 November 2012 02:52 Go to previous message
Michel Cadot
Messages: 59127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if u can make sure correct order.


With ORDER BY NULL how can you say you have a sure order?
As I said ALL answers, I repeat ALL ANSWERS (including the 2 previous ones) are wrong unless there is a complete order defined on ALL the rows.

Regards
Michel
Previous Topic: single-row subquery returns more than one row
Next Topic: The problem of use Order By
Goto Forum:
  


Current Time: Thu Sep 18 11:35:57 CDT 2014

Total time taken to generate the page: 0.07023 seconds