Home » SQL & PL/SQL » SQL & PL/SQL » Converting columns into rows
Converting columns into rows [message #315132] Sat, 19 April 2008 05:53 Go to next message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member

hi all

I have a table "attd" having columns

emp_id
d_date
status

i am having problem in getting the data in the form:

Emp_id date Status
Imran 04-Jul-2007 P
Amir 04-Jul-2008 L
Ali 04-Jul-2008 A
Imran 18-Jul-2007 P

I tried a lot to change column into rows but i couldn't solve it

My tried Sql is

select a.emp_id,max(decode(a.r,1,a.d_date))
||max(decode(a.r,3,' ' || a.status)) Attendance
from (select emp_id,d_date,row_number() over (Partition by emp_id order by emp_id) r
from attd ) a
group by emp_id

The query gives me the result like

Imran P P
Amir L
Ali A

anyone help will be appreciated
Re: Converting columns into rows [message #315172 is a reply to message #315132] Sat, 19 April 2008 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am having problem in getting the data in the form:

Why not just "select * from table"?

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

And post a test case: create table and insert statements and the result you want with these data.

Regards
Michel
Re: Converting columns into rows [message #315249 is a reply to message #315172] Sun, 20 April 2008 07:30 Go to previous messageGo to next message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member

Sir

I have a table "attd"

create table attd(
emp_id varchar(20),
d_date date,
status char(1))

insert into attd values('a','20-Apr-2008','P');
insert into attd values('b','18-Apr-2008','A');
insert into attd values('a','18-Apr-2008','A');
insert into attd values('b','19-Apr-2008','L');
insert into attd values('c','20-Apr-2008','A');

I want to generate a report which should looks like


Emp_id       18-Apr-2008       19-Apr-2008      20-Apr-2008
a                 A                -                  P
b                 A                L                  -
c                 -                -                  A



I hope that you will help me to get out of this situation
Re: Converting columns into rows [message #315250 is a reply to message #315132] Sun, 20 April 2008 07:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
search this forum for "pivot query"
Re: Converting columns into rows [message #315251 is a reply to message #315249] Sun, 20 April 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Isn't is better when you show us what you want and when it is formatted.
Ana gave you the answer, it is classical pivot problem.

Regards
Michel
Re: Converting columns into rows [message #387588 is a reply to message #315132] Thu, 19 February 2009 23:46 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey,

I donno whether its solved or not..but i was doing practise, and i executed it..quite easy

SELECT emp_id,
MAX (DECODE (TO_CHAR (d_date, 'DD-MON-YYYY'), '18-APR-2008', status)
) date1,
MAX (DECODE (TO_CHAR (d_date, 'DD-MON-YYYY'), '19-APR-2008', status)
) date2,
MAX (DECODE (TO_CHAR (d_date, 'DD-MON-YYYY'), '20-APR-2008', status)
) date3
FROM attd
GROUP BY emp_id

Regards,
Ashoka BL
Re: Converting columns into rows [message #387604 is a reply to message #387588] Fri, 20 February 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What has been posted is also for you:
Michel Cadot wrote on Sat, 19 April 2008 18:52
...
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
...
Regards
Michel

Re: Converting columns into rows [message #387608 is a reply to message #315132] Fri, 20 February 2009 00:48 Go to previous message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

My mistake Michel...i just forgotten once... Smile

Regards,
Ashoka BL
Previous Topic: Creating External table Using remote system folder
Next Topic: clob and ref cursor
Goto Forum:
  


Current Time: Fri Dec 09 19:16:13 CST 2016

Total time taken to generate the page: 0.08830 seconds