Display row values as column header

oracle, oracle10g, plsql, sql

Solution

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.

Problem

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?

Original source