将行值显示为列标题

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:

我需要一个输出,其中DATE和WORKERNAME的输出将是列标题,如下所示:

    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?

有人可以建议如何使用oracle SQL或PL / SQL实现这一目标吗?

3 个解决方案

#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:

在使用纯SQL甚至PL / SQL之后生成输出并不是那么优雅。如果让客户做这项工作会更好。根据您希望将最终输出呈现给最终用户的方式,您可以选择从简单的SQL * PLUS到更复杂的报告工具。这是一个如何使用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:

如果需要生成包含不同工作名或/和不同日期的报表,则可以使用SQL * PLUS命令中断来中断特定列或列组合的报表。例如:

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.

以下是SQL * PLUS用户指南,您可以在其中找到有关上述示例中使用的任何命令的详细信息。

#2

Maybe using pivot would be useful for you.

也许使用pivot会对你有用。

    DECLARE @cols AS NVARCHAR(MAX),
@colsName AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(WORKERNAME +'_'+convert(varchar(50),[DATE]) +'_'+c.col) 
                  from TRY
                  cross apply 
                  (
                    select 'FREE' col
                    union all
                    select 'USED'
                  ) c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
select @colsName 
= STUFF((SELECT distinct ', ' + QUOTENAME(WORKERNAME +'_'+convert(varchar(50),[DATE])+'_'+c.col) 
           +' as ['
           + WORKERNAME + case when c.col = 'FREE' then '_'+convert(varchar(50),[DATE])+'_FREE]' else '_'+convert(varchar(50),[DATE])+'_USED]' end
         from TRY
         cross apply 
         (
            select 'FREE' col
            union all
            select 'USED'

         ) c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT ID, ' + @colsName + ' 
 from 
 (
  select 
    ID, 
    WORKERNAME +''_''+convert(varchar(50),[DATE]) +''_''+col col, 
    value
  from
  (
    select ID, 
      WORKERNAME, 
      date,
    cast(FREE as numeric(10, 2)) FREE,
  cast(USED as numeric(10, 2)) USED
    from TRY
  ) src
  unpivot
  (
    value
    for col in (FREE, USED)
  ) unpiv
 ) s
 pivot 
 (
   sum(value)
   for col in (' + @cols + ')
 ) p 
 order by ID'

execute(@query)

#3

TTITLE LEFT 'Manager: ' MGRVAR SKIP 2
BREAK ON MANAGER_ID SKIP PAGE
BTITLE OFF


SELECT MANAGER_ID, DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE MANAGER_ID IN (101, 201)
ORDER BY MANAGER_ID, DEPARTMENT_ID;


Manager:       101

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           10 Whalen                          4400
           40 Mavris                          6500
           70 Baer                           10000
          100 Greenberg                      12000
          110 Higgins                        12000

Manager:       201

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Fay                             6000

6 rows selected.

See if something like this helps you.

看看这样的事情是否对你有帮助。