转置查询输出 [英] Transpose a query output

问题描述

我有一个普通的选择查询，它会在输出后产生结果.

I have a normal select query which results following output.

select cid,x1,x2,x3,x4,fy
  from temp_table;

cid     x1  x2  x3  x4  fy
----------------------------
6657    100 0   0   200 2014
6658    300 0   0   400 2015
6659    500 0   0   600 2016

我希望它在输出之后将其重写为打印内容.

I want it to rewrite it print following output.

    2014    2015    2016    
-------------------------   
x1  100     300     500     
x2  0       0       0       
x3  0       0       0       
x4  200     400     600 

如何实现?

推荐答案

这里是一种仅通过子查询和聚合来完成此操作的方法:

Here is a way to do this with just subqueries and aggregation:

select name,
       sum(case when fy = 2014 then x end) as "2014",
       sum(case when fy = 2015 then x end) as "2015",
       sum(case when fy = 2016 then x end) as "2016"
from (select fy,
             (case when n.n = 1 then 'x1'
                   when n.n = 2 then 'x2'
                   when n.n = 3 then 'x3'
                   when n.n = 4 then 'x4'
              end) as name,
             (case when n.n = 1 then x1
                   when n.n = 2 then x2
                   when n.n = 3 then x3
                   when n.n = 4 then x4
              end) as x
      from temp_table cross join
            (select 1 as n from dual union all
             select 2 from dual union all
             select 3 from dual union all
             select 4 from dual
            ) n
     ) t
group by name;

您也可以使用pivot，但这是Oracle SQL的最新功能，因此我倾向于使用此方法.

You can also use pivot, but that is a very recent addition to Oracle SQL, so I'm inclined to use this method.

这篇关于转置查询输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！