Display row values as column header

Question

I have a select statement

SELECT * 
  FROM TABLENAME 
 WHERE WORKERNAME = 'A'
   AND DATE = '12/17/2014'

The output will be:

FREE | USED | DATE      | WORKERNAME
------------------------------------
  1  |  0   |12/17/2014 | A
  1  |  0   |12/17/2014 | A      
  1  |  0   |12/17/2014 | A     

I need to have an output where outputs for DATE and WORKERNAME will be column header that will look like:

    A
----------
12/17/2014
----------
FREE | USED
----------
1    | 0
1    | 0
1    | 0

Can someone suggest how this could be achieved using an oracle SQL or PL/SQL?

Answer 1

It would not be that elegant to produce the output you are after using pure SQL or even PL/SQL. It would be better if you let a client do the work. Depending on how you want to present your final output to an end user your choices are ranging from simple SQL*PLUS to a more sophisticated reporting tools. Here is a simple example of how you can produce that output using SQL*PLUS:

clear screen;

column workername new_value worker_name;
column date1 new_value d1;
column workername noprint;
column date1 noprint;
set linesize 15;
column free format a7;
column used format a7;
ttitle center worker_name skip 1 -
center '------------' skip 1 -
center d1 skip 1 -
center '------------' skip 1;
set colsep '|'

/* sample of data from your question */
with t1(free, used, date1, workername) as(
   select 1, 0, date '2014-12-17', 'A' from dual union all
   select 1, 0, date '2014-12-17', 'A' from dual union all
   select 1, 0, date '2014-12-17', 'A' from dual
)
select to_char(free) as free
     , to_char(used) as used
     , to_char(date1, 'mm/dd/yyyy') as date1
     , workername
  from t1
 where workername = 'A'
   and date1 = date '2014-12-17';

Result:

       A       
  ------------ 
   12/17/2014   
  ------------ 
 FREE   |USED   
 -------|-------
 1      |0      
 1      |0      
 1      |0  

If there is a need to produce a report that includes different workernames or/and different date, the break on SQL*PLUS command can be used to break report on a specific column or a combination of columns. For example:

column workername new_value worker_name;
column date1 new_value d1;
column workername noprint;
column date1 noprint;
set linesize 15;
column free format a7;
column used format a7;
ttitle center worker_name skip 1 -
center '------------' skip 1 -
center d1 skip 1 -
center '------------' skip 1;
set colsep '|'
break on worker_name skip page on date1 skip page;

/* sample of data  */
with t1(free, used, date1, workername) as(
   select 1, 0, date '2014-12-17', 'A' from dual union all
   select 1, 0, date '2014-11-17', 'A' from dual union all
   select 1, 0, date '2014-12-17', 'A' from dual union all
   select 1, 0, date '2014-11-17', 'B' from dual
)
select to_char(free) as free
     , to_char(used) as used
     , to_char(date1, 'mm/dd/yyyy') as date1
     , workername
  from t1
 order by workername, date1;

Result:

       A       
  ------------ 
   11/17/2014  
  ------------ 
FREE   |USED   
-------|-------
1      |0      

       A       
  ------------ 
   12/17/2014  
  ------------ 
FREE   |USED   
-------|-------
1      |0      
1      |0      

       B       
  ------------ 
   11/17/2014  
  ------------ 
FREE   |USED   
-------|-------
1      |0      

Here is the SQL*PLUS user's guide where you can find detailed information on any command that's been used in the above examples.