Home » SQL & PL/SQL » SQL & PL/SQL » how to transpose the rows in oracle
how to transpose the rows in oracle [message #248955] Mon, 02 July 2007 21:51 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,

select count(deptno) from emp where hiredate between
to_date('10/01/1980','mm/dd/yyyy') and to_date('3/31/1981','mm/dd/yyyy')
group by deptno
union all
select count(deptno) from emp where hiredate between
to_date('4/1/1981','mm/dd/yyyy') and to_date('9/30/1981','mm/dd/yyyy')
group by deptno
union all
select count(deptno) from emp where hiredate between
to_date('10/1/1981','mm/dd/yyyy') and to_date('3/31/1982','mm/dd/yyyy')
group by deptno
union all
select count(deptno) from emp where hiredate between
to_date('4/1/1982','mm/dd/yyyy') and to_date('9/30/1982','mm/dd/yyyy')
group by deptno
union all
select count(deptno) from emp where hiredate between
to_date('10/1/1982','mm/dd/yyyy') and to_date('3/31/1983','mm/dd/yyyy')
group by deptno

----

----


union all
select count(deptno) from emp where hiredate between
to_date('10/1/2006','mm/dd/yyyy') and to_date('3/31/2007','mm/dd/yyyy')
group by deptno

first thing is how to change this query dynamically.
I am adding 6 months to each query.Insteadof hardcoded values,how to add them dynamically because if I do this way till the current year it will almost 52 queries with unionall.

one more thing
I need my output horizontally for e.g.
the output of above query using unionall is

COUNT(DEPTNO)
-------------
1
2
1
1
3
2
1
1

for individual queries it is

COUNT(DEPTNO)
-------------
1
2

COUNT(DEPTNO)
-------------
1
1
3

COUNT(DEPTNO)
-------------
2
1
1
now my output should be
deptno(10) deptno(20) deptno(30)
-------------------------------------------------------
1 2 0
1 1 3
2 1 1

my query should check the date ranges every six months till the current date and the begin date is 10/1/1980.begin date is hardcoded and from there should be added every six months.

thanks
Re: how to transpose the rows in oracle [message #248956 is a reply to message #248955] Mon, 02 July 2007 21:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Search for "pivot query"
Re: how to transpose the rows in oracle [message #248961 is a reply to message #248955] Mon, 02 July 2007 23:22 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Instead of the UNIONS try using an Integer Series Generator. Here's one example.

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 3 00:20:45 2007

-- to include periods when no emps were hired, do this

select
  dates.period_start_date,
  emp.deptno,
  sum
  ( case
    when emp.hiredate between dates.period_start_date and add_months( dates.period_start_date, 6 )
    then 1
    else 0
    end
  ) as emps_hired
from
  scott.emp emp,
  ( select add_months( date '1980-01-01', 6*(level-1) ) as period_start_date
    from dual
    connect by level <= 10
  ) dates
group by
  period_start_date,
  emp.deptno
order by 1, 2 ;

PERIOD_STA     DEPTNO EMPS_HIRED
---------- ---------- ----------
1980-01-01         10          0
1980-01-01         20          0
1980-01-01         30          0
1980-07-01         10          0
1980-07-01         20          1
1980-07-01         30          0
1981-01-01         10          1
1981-01-01         20          1
1981-01-01         30          3
1981-07-01         10          1
1981-07-01         20          1
1981-07-01         30          3
1982-01-01         10          1
1982-01-01         20          0
1982-01-01         30          0
1982-07-01         10          0
1982-07-01         20          0
1982-07-01         30          0
1983-01-01         10          0
1983-01-01         20          0
1983-01-01         30          0
1983-07-01         10          0
1983-07-01         20          0
1983-07-01         30          0
1984-01-01         10          0
1984-01-01         20          0
1984-01-01         30          0
1984-07-01         10          0
1984-07-01         20          0
1984-07-01         30          0

30 rows selected.


-- to exclude periods when no emps were hired do this

select
  dates.period_start_date,
  emp.deptno,
  count(empno) as emps_hired
from
  scott.emp emp,
  ( select add_months( date '1980-01-01', 6*(level-1) ) as period_start_date
    from dual
    connect by level <= 10
  ) dates
where
  emp.hiredate between
    dates.period_start_date and
    add_months( dates.period_start_date, 6 )
group by
  period_start_date,
  emp.deptno
order by 1, 2;

PERIOD_STA     DEPTNO EMPS_HIRED
---------- ---------- ----------
1980-07-01         20          1
1981-01-01         10          1
1981-01-01         20          1
1981-01-01         30          3
1981-07-01         10          1
1981-07-01         20          1
1981-07-01         30          3
1982-01-01         10          1

8 rows selected.



To transpose rows to columns read SQL Snippets: SQL Techniques Tutorials - Rows to Columns.

--
Joe Fuda
SQL Snippets
Re: how to transpose the rows in oracle [message #248967 is a reply to message #248955] Tue, 03 July 2007 00:10 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,

as I mentioned (union all queries)
based on the first query values
which are
1
2

and second query values

1
1
3

for every six months values whichever I get, those should be displayed horizontally.
my output should be as below

1 2 0
1 1 3
Re: how to transpose the rows in oracle [message #248979 is a reply to message #248967] Tue, 03 July 2007 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

And the answer is... search for "pivot".

Regards
Michel
Re: how to transpose the rows in oracle [message #248989 is a reply to message #248979] Tue, 03 July 2007 00:55 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
I haven't used pivot before,one more thing
as I mentioned my rows are based on every 6 months

count(empno)
1
2

count(empno)
1
1
3

tried with the query you sent and I am getting the output as below
count(empno)
1
2
1
1
3
Re: how to transpose the rows in oracle [message #248995 is a reply to message #248989] Tue, 03 July 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then you pivot.

Regards
Michel
Re: how to transpose the rows in oracle [message #248999 is a reply to message #248995] Tue, 03 July 2007 01:14 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,

i executed the query below

select
dates.period_start_date,
emp.deptno,
count(empno) as emps_hired
from
scott.emp emp,
( select add_months( date '1980-10-01', 6*(level-1) ) as period_start_date
from dual
connect by level <= 100
) dates
where
emp.hiredate between
dates.period_start_date and
add_months( dates.period_start_date, 6 )
group by
period_start_date,
emp.deptno;


and the output is
DEPTNO EMPS_HIRED
---------- ----------
20 1
30 2
10 1
20 1
30 3
10 2
20 1
30 1
20 2
10 1
10 1

11 rows selected.

now, I pivoted it and the output is
1 2 1 1 3 2 1 1 2 1 1
but I dont want like this
I need as records based on break of deptno

dept(10) dept(20) dept(30)
----------------------------
0 1 2
1 1 3
2 1 1
0 2 0
1 0 0
1 0 0
Re: how to transpose the rows in oracle [message #249015 is a reply to message #248999] Tue, 03 July 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't see you format your post.

Pivot per you period.

Regards
Michel
Re: how to transpose the rows in oracle [message #249017 is a reply to message #249015] Tue, 03 July 2007 01:40 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
how do I pivot per period?

thanks
Re: how to transpose the rows in oracle [message #249019 is a reply to message #249017] Tue, 03 July 2007 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't answer to people that don't follow the stickies and don't answer to the question.
Moreover the link posted by Joe contains the answer, just read it.

Regards
Michel
Re: how to transpose the rows in oracle [message #249028 is a reply to message #248955] Tue, 03 July 2007 02:06 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
I tried as below

select
decode(deptno,10,count(empno),null) as v1,
decode(deptno,20,count(empno),null) as val2,
decode(deptno,30,count(empno),null) as val3
from
scott.emp emp,
( select add_months( date '1980-10-01', 6*(level-1) ) as period_start_date
from dual
connect by level <= 100
) dates
where
emp.hiredate between
dates.period_start_date and
add_months( dates.period_start_date, 6 )
group by period_start_date,
emp.deptno

and my output is

V1 VAL2 VAL3
---------- ---------- ----------
1
2
1
1
3
2
1
1
2
1
1

11 rows selected.

But I need as below

V1 VAL2 VAL3
---------- ---------- ----------
0 1 2
1 1 3
2 1 1
0 2 0
1 0 0
1 0 0

So I tried as below

select
max(decode(deptno,10,count(empno),null) )as v1,
max(decode(deptno,20,count(empno),null)) as val2,
max(decode(deptno,30,count(empno),null)) as val3
from
scott.emp emp,
( select add_months( date '1980-10-01', 6*(level-1) ) as period_start_date
from dual
connect by level <= 100
) dates
where
emp.hiredate between
dates.period_start_date and
add_months( dates.period_start_date, 6 )
group by period_start_date,
emp.deptno
/

but I am getting my output as below which I am not expecting

V1 VAL2 VAL3
---------- ---------- ----------
2 2 3

my output should be as below


V1 VAL2 VAL3
---------- ---------- ----------
0 1 2
1 1 3
2 1 1
0 2 0
1 0 0
1 0 0
Re: how to transpose the rows in oracle [message #249036 is a reply to message #249028] Tue, 03 July 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your problem with formatting your post?

Regards
Michel
Re: how to transpose the rows in oracle [message #249065 is a reply to message #248955] Tue, 03 July 2007 04:12 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
any ideas,
problem still not solved.

executed the query below

select
decode(deptno,10,count(empno),null) as v1,
decode(deptno,20,count(empno),null) as val2,
decode(deptno,30,count(empno),null) as val3
from
scott.emp emp,
( select add_months( date '1980-10-01', 6*(level-1) ) as period_start_date
from dual
connect by level <= 100
) dates
where
emp.hiredate between
dates.period_start_date and
add_months( dates.period_start_date, 6 )
group by period_start_date,
emp.deptno


output is

V1 VAL2 VAL3
---------- ---------- ----------
1
2
1
1
3
2
1
1
2
1
1

but I want as below


V1 VAL2 VAL3
---------- ---------- ----------
0 1 2
1 1 3
2 1 1
0 2 0
1 0 0
1 0 0


thanks
Re: how to transpose the rows in oracle [message #249066 is a reply to message #248955] Tue, 03 July 2007 04:21 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
Executed the query below 

select  
decode(deptno,10,count(empno),null) as v1,
decode(deptno,20,count(empno),null) as val2,
decode(deptno,30,count(empno),null) as val3
from
  scott.emp emp,
  ( select add_months( date '1980-10-01', 6*(level-1) ) as period_start_date
    from dual
    connect by level <= 100
  ) dates
where
  emp.hiredate between
    dates.period_start_date and
    add_months( dates.period_start_date, 6 )
group by  period_start_date,
  emp.deptno

output of the above query

        V1       VAL2       VAL3
---------- ---------- ----------
                    1
                               2
         1
                    1
                               3
         2
                    1
                               1
                    2
         1
         1


but I want my output as below


        V1       VAL2       VAL3
---------- ---------- ----------
        0         1           2
        1         1           3 
        2         1           1
        0         2           0
        1         0           0 
        1         0           0

thanks
Re: how to transpose the rows in oracle [message #249068 is a reply to message #249066] Tue, 03 July 2007 04:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Steve,
you're nearly there. But I would use SUM in combination with decode. And I wouldn't group by the departments (you are splitting them into separate columns anyway). So, here's a slightly modified query. Have a look at it.

SELECT   SUM(DECODE(deptno, 10, 1, 0)) AS v1
       , SUM(DECODE(deptno, 20, 1, 0)) AS val2
       , SUM(DECODE(deptno, 30, 1, 0)) AS val3
FROM     scott.emp emp
       , (SELECT ADD_MONTHS ( TO_DATE('1980-10-01', 'YYYY-MM-DD')
                            , 6 * (LEVEL - 1)
                            ) AS period_start_date
          FROM   DUAL
          CONNECT BY LEVEL <= 100) dates
WHERE    emp.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
GROUP BY period_start_date
/

MHE

[Updated on: Tue, 03 July 2007 04:30]

Report message to a moderator

Re: how to transpose the rows in oracle [message #249164 is a reply to message #248955] Tue, 03 July 2007 09:47 Go to previous message
stevefaulk
Messages: 36
Registered: June 2007
Member
thanx a lot
Previous Topic: Get unique rows from table
Next Topic: SQL doubt
Goto Forum:
  


Current Time: Thu Dec 08 02:09:56 CST 2016

Total time taken to generate the page: 0.12025 seconds