Home » SQL & PL/SQL » SQL & PL/SQL » Viewing formatted data from a table (Oracle 9i, Windows XP)
Viewing formatted data from a table [message #327272] Sun, 15 June 2008 22:31 Go to next message
saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka
Junior Member
Dear Friends,
I am facing a problem about viewing data from a table. The table is like this

Date	      Type    R_Count	L_Count
23/05/2008	A	100	50
24/05/2008	B	200	60
25/05/2008	C	150	40
26/05/2008	A	300	60
27/05/2008	C	400	100
23/05/2008	B	200	50
24/05/2008	C	100	40
25/05/2008	A	60	40

The output should be like this

Date   A_R_Count A_L_Count B_R_Cnt B_L_Cnt C_R_Cnt  C_L_Cnt

23/05/08    100     50        200     50              
24/05/08                      200     60    100        40
25/05/08     60     40                      150        40  

…………

I need suggestion to solve this problem.



[mod-edit: code tags added]

[Updated on: Sun, 15 June 2008 22:46] by Moderator

Report message to a moderator

Re: Viewing formatted data from a table [message #327277 is a reply to message #327272] Sun, 15 June 2008 22:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Search the forum for PIVOT.
Re: Viewing formatted data from a table [message #327306 is a reply to message #327272] Mon, 16 June 2008 00:23 Go to previous message
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
Junior Member
SQL> select * from faq;

EDATE     T    R_COUNT    L_COUNT
--------- - ---------- ----------
23-MAY-08 A        100         50
24-MAY-08 B        200         60
25-MAY-08 C        150         40
26-MAY-08 A        300         60
27-MAY-08 C        400        100
23-MAY-08 B        200         50
24-MAY-08 C        100         40
25-MAY-08 A         60         40



SQL> select edate,
  2  max(decode(type,'A',r_count,null)) A_R_COUNT,
  3  max(decode(type,'B',r_count,null)) B_R_COUNT,
  4  max(decode(type,'C',r_count,null)) C_R_COUNT,
  5  max(decode(type,'A',l_count,null)) A_L_COUNT,
  6  max(decode(type,'B',l_count,null)) B_L_COUNT,
  7  max(decode(type,'C',l_count,null)) C_L_COUNT
  8  from faq
  9  group by edate
 10  order by edate;

EDATE      A_R_COUNT  B_R_COUNT  C_R_COUNT  A_L_COUNT  B_L_COUNT  C_L_COUNT
--------- ---------- ---------- ---------- ---------- ---------- ----------
23-MAY-08        100        200                    50         50
24-MAY-08                   200        100                    60         40
25-MAY-08         60                   150         40                    40
26-MAY-08        300                               60
27-MAY-08                              400                              100

i think this is what you are looking for.
Previous Topic: Dictionary table to see the operation of a procedure
Next Topic: One-time-only procedure doubt
Goto Forum:
  


Current Time: Fri Dec 09 23:04:00 CST 2016

Total time taken to generate the page: 0.09640 seconds