Home » SQL & PL/SQL » SQL & PL/SQL » How To display 2 rows in a single row (Oracle 10.2.0.1.0,Windows)
How To display 2 rows in a single row [message #298890] Fri, 08 February 2008 03:09 Go to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Hi All,

I want to display two rows in a single row output by a SQL statement.

For Example
Sr_No	Flow	Cal1	SeqNo	Appl
1	WF1	CALL1	3262	MA
2	WF1	CALL1	3263	CA


I need the output as below

Sr_No	Flow	Cal1    SeqNo Appl Sr_No Flow Call1 SeqNo App1
1       WF1     CALL1   3262  MA   2     WF1  CALL1 3263  CA  


Kindly give your idea.

Thank You
Regards,
Dinesh

[Updated on: Fri, 08 February 2008 03:10]

Report message to a moderator

Re: How To display 2 rows in a single row [message #298893 is a reply to message #298890] Fri, 08 February 2008 03:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This totally depends on the what it actually is you want.
What you described can be done by using the base-table twice in the from-clause.
Re: How To display 2 rows in a single row [message #298896 is a reply to message #298890] Fri, 08 February 2008 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a starting point:
SQL> select level-1 rn from dual connect by level <= 10;
        RN
----------
         0
         1
         2
         3
         4
         5
         6
         7
         8
         9

10 rows selected.

SQL> with data as ( select level-1 rn from dual connect by level <= 10 )
  2  select max(decode(mod(rownum,2),1,rn)) col1,
  3         max(decode(mod(rownum,2),0,rn)) col2
  4  from data
  5  group by trunc(rn/2)
  6  order by trunc(rn/2)
  7  /
      COL1       COL2
---------- ----------
         0          1
         2          3
         4          5
         6          7
         8          9

5 rows selected.

Regards
Michel
Re: How To display 2 rows in a single row [message #298898 is a reply to message #298890] Fri, 08 February 2008 03:48 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member

SQL> select bill_no,item_cd from sale;

   BILL_NO    ITEM_CD
---------- ----------
         1       3004
         2       3004
         3       3004
         4       3005
         5       3004
         6       3005
         7       3005
         8       3005
        10       4005
         9       4005

10 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select a.bill_no,a.item_cd,b.bill_no,b.item_cd from sale a,sale b
  2  where mod(a.bill_no,2)>0
  3  and mod(b.bill_no,2)=0
  4* and b.bill_no=a.bill_no+1
SQL> /

   BILL_NO    ITEM_CD    BILL_NO    ITEM_CD
---------- ---------- ---------- ----------
         1       3004          2       3004
         3       3004          4       3005
         5       3004          6       3005
         7       3005          8       3005
         9       4005         10       4005


regards,
Re: How To display 2 rows in a single row [message #298929 is a reply to message #298898] Fri, 08 February 2008 05:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What makes you guys think that the original poster wants first plus second, third plus fourth, etc in a single row?

Solutions provided (ooohh.. in newbies forum!) without a clue about the actual problem..
Re: How To display 2 rows in a single row [message #298932 is a reply to message #298890] Fri, 08 February 2008 05:28 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Hi All,

Thanks for your valuable answers.

But I want to add two rows into a single row.

Lets say I have the below records in a table:

BILL_NO    ITEM_CD
---------- ----------
         1       3004
         2       3004


I want the output as below:

BILL_NO    ITEM_CD    BILL_NO    ITEM_CD
---------- ---------- ---------- ----------
         1       3004          2       3004


Thanks A lot.

With Regards,
Dinesh
Re: How To display 2 rows in a single row [message #298935 is a reply to message #298890] Fri, 08 February 2008 05:45 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
you have repeated yourself again
and not described about what actually you want(purpose) as table with only two rows are confusing me.the query written by me was
clueless( I am agree with frank),it was my own assuption same as michel.may you explain it?

regards,
Re: How To display 2 rows in a single row [message #298941 is a reply to message #298929] Fri, 08 February 2008 05:57 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solution, oh no, just starting point.
As you said, we don't know which rows have to be in the same line.

@Dinesh, now you have starting points, show us what you can do with it.

Regards
Michel
Previous Topic: ora-04091:table user.table1 is mutating,trigger function may not see
Next Topic: How to execute Oracle function in vb
Goto Forum:
  


Current Time: Sat Dec 10 10:31:24 CST 2016

Total time taken to generate the page: 0.09429 seconds