OraFAQ Forum: Reports & Discoverer » Converting columns into rows

Home » Developer & Programmer » Reports & Discoverer » Converting columns into rows (developer 6i,oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Converting columns into rows [message #315131]

Sat, 19 April 2008 05:49

imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad

Member

hi all

I have a table 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

[EDITED by LF; added [code] tags]

Please, FORMAT your code and use [code] tags to improve readability; see how your query looks like (unreadable), and how it should look like. Next time do it yourself! If you don't know how, read it in the OraFAQ Forum Guide.

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

[Updated on: Sat, 19 April 2008 10:40] by Moderator

Report message to a moderator

Re: Converting columns into rows [message #315167 is a reply to message #315131]

Sat, 19 April 2008 10:46

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OK, you told us what you have (at least, that's what I understood); you told us what is the result you got.

But, you didn't say what you WANT; how does desired output look like?

Report message to a moderator

Re: Converting columns into rows [message #315248 is a reply to message #315167]

Sun, 20 April 2008 07:20

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

[Updated on: Sun, 20 April 2008 07:31]

Report message to a moderator

Re: Converting columns into rows [message #315284 is a reply to message #315248]

Sun, 20 April 2008 15:37

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, a simple pivoting (if all dates are known) is rather simple:

SQL> column a_18 format a4
SQL> column a_19 format a4
SQL> column a_20 format a4
SQL> l
  1  SELECT * FROM
  2  (SELECT emp_id,
  3     MAX(DECODE
  4          (d_date, TO_DATE('18.04.2008', 'dd.mm.yyyy'), status, '-')
  5        ) a_18,
  6     MAX(DECODE
  7          (d_date, TO_DATE('19.04.2008', 'dd.mm.yyyy'), status, '-')
  8        ) a_19,
  9     MAX(DECODE
 10          (d_date, TO_DATE('20.04.2008', 'dd.mm.yyyy'), status, '-')
 11        ) a_20
 12   FROM ATTD
 13   GROUP BY emp_id
 14* )
SQL> /

EMP_ID               A_18 A_19 A_20
-------------------- ---- ---- ----
a                    A    -    P
b                    A    L    -
c                    -    -    A

SQL>

However, if it is more complicated, I'd suggest you to read this AskTom article about pivot query.

But! If your question regards Reports Builder, you are quite lucky - just create a Matrix report and enjoy.

Report message to a moderator

Re: Converting columns into rows [message #315357 is a reply to message #315284]

Mon, 21 April 2008 05:07

imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad

Member

thanks

i have done it

Report message to a moderator

Previous Topic:	Report to Excel Conversion
Next Topic:	getting missing numbers in report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 14:57:19 CDT 2024