How to turn rows into columns
March 13th, 2006 By Eddie Awad
Consider this query:
select job_id, count(*)
from employees
where job_id in ('AD_PRES', 'AD_VP', 'IT_PROG')
group by job_id
It returns this result set:
JOB_ID COUNT(*) ---------- ---------------------- AD_PRES 1 AD_VP 2 IT_PROG 5 3 rows selected
Now, you want to transpose the result set and turn the JOB_ID values into columns in a single row. Here is one way of doing it:
select sum(case when job_id='AD_PRES' then 1 else 0 end) as AD_PRES,
sum(case when job_id='AD_VP' then 1 else 0 end) as AD_VP,
sum(case when job_id='IT_PROG' then 1 else 0 end) as IT_PTOG
from (
select job_id
from employees
where job_id in ('AD_PRES', 'AD_VP', 'IT_PROG'))
The result:
AD_PRES AD_VP IT_PTOG ---------------------- ---------------------- ---------------------- 1 2 5 1 rows selected
April 2nd, 2006 at 12:04 am
I tried the previous code to change rows into columns , but it did not work, I think it may work with later versions of sql.
Im using sql 8.0.
the code was:
SQL> select job, count(*) from emp
2 group by job;
JOB COUNT(*)
——— ———
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
SQL> select sum(case when job = ‘ANALYST’ then 1 else 0 end) as ANALYST,
2 sum(case when job = ‘CLERK’ then 1 else 0 end) as CLERK,
3 sum(case when job = ‘MANAGER’ then 1 else 0 end) as MANAGER,
4 sum(case when job = ‘PRESIDENT’ then 1 else 0 end) as PRESIDENT,
5 sum(case when job = ‘SALESMAN’ then 1 else 0 end) as SALESMAN
6 from (
7 select job
8 from emp
9 where job in (’ANALYST’, ‘CLERK’, ‘MANAGER’, ‘PRESIDENT’, ‘SALESMAN’))
10 /
But the ERROR was
select sum(case)when job = ‘ANALYST’ then 1 else 0 end) as ANLYST,
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
I don’t know what is the problem..
April 2nd, 2006 at 12:06 am
This was the error, and not the previous one:
select sum(case when job = ‘ANALYST’ then 1 else 0 end) as ANALYST,
*
ERROR at line 1:
ORA-00907: missing right parenthesis
April 6th, 2006 at 9:10 am
Hi,
the simple case expression was released in 8.1
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/wnsql.htm#970400
Greetings
Karl
April 11th, 2006 at 5:37 am
What if you do not know the value of the columns in advance?
April 11th, 2006 at 6:09 pm
Deepak, you mean if the number of rows that we want to convert into columns is unknown or dynamic? In that case, We can dynamically construct the query that contains the CASE expressions, then execute the query (string) as dynamic SQL (using execute immediate). I have not tried it but theoretically it should work.
More info at
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:766825833740
Does the select list have a maximum number of columns?