Home » Developer & Programmer » Reports & Discoverer » Converting columns into rows (developer 6i,oracle 9i)
Converting columns into rows [message #315131] Sat, 19 April 2008 05:49 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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?
Re: Converting columns into rows [message #315248 is a reply to message #315167] Sun, 20 April 2008 07:20 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

[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 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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.
Re: Converting columns into rows [message #315357 is a reply to message #315284] Mon, 21 April 2008 05:07 Go to previous message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member

thanks

i have done it

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


Current Time: Tue Dec 06 16:22:23 CST 2016

Total time taken to generate the page: 0.16861 seconds