OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

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

5 Responses to “How to turn rows into columns”

  1. Balqees Says:

    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..

  2. Balqees Says:

    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

  3. Karl Reitschuster Says:

    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

  4. deepak Says:

    What if you do not know the value of the columns in advance?

  5. Eddie Awad Says:

    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?

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question