oracle 将标头包含在 SQL 查询的结果中

to include a header into result of sQL query

提问by y2j

i m running a sql query to select data from a table .

我正在运行 sql 查询以从表中选择数据。

i want to include headers as the first row in the result of the query. headers will be coming from some other table or can we can sqlhardcode the values .

我想在查询结果中包含标题作为第一行。标头将来自其他一些表，或者我们可以对值进行 sqlhardcode。

below is my query to fetch data.

下面是我获取数据的查询。

 select invoice_type_code,
  shipper_short_code ,
  charge_type_code ,
  incurred_date  ,
  charge_amount  , 
  charge_description 
  from prepayment_charge_calc ;

i want a header above data of first row of every column.these header shouldnt be the column name

我想要每列第一行数据上方的标题。这些标题不应该是列名

for eg.

例如。

header1   header2   header3  header4   header5  header6  
 1          2           3          4        5        6

header 1 to 6 are coming from other table or can be the harcoded value .below this header we should have the data extracted from "prepayment_charge_calc" table .. 1,2,3,4 are the data from "prepayment_charge_calc" table

标题 1 到 6 来自其他表或可以是硬编码值。在此标题下方，我们应该有从“prepayment_charge_calc”表中提取的数据.. 1,2,3,4 是来自“prepayment_charge_calc”表的数据

can any one suggest me the query for this .

任何人都可以建议我对此进行查询。

采纳答案by Lalit Kumar B

I don't see any extra effort to have the column names in the header the way you want, just mention an ALIASto the columns the way you want.

我没有看到任何额外的努力以您想要的方式在标题中包含列名，只需按照您想要的方式提及ALIAS列。

For example :

例如 ：

select col1 as "header1", col2 as "header2".... from table

select col1 as "header1", col2 as "header2".... from table

In any GUI/non GUI based tool, the scroll paneautomatically places the table headerat the top of the resultset, which is actually the column headers you need.

在 any 中GUI/non GUI based tool，scroll pane自动将table header放在 的顶部resultset，这实际上是您需要的列标题。

回答by toddlermenot

Assuming Oracle DBMS, you can create the header row manually using DUAL table and then union with the real data. Use a dummy psuedo-column ("rno" in the example below) to sort the data. However, you have to convert any other datatype to VARCHAR to make this work. Idea is illustrated below:

假设使用 Oracle DBMS，您可以使用 DUAL 表手动创建标题行，然后与真实数据合并。使用虚拟伪列（下例中的“rno”）对数据进行排序。但是，您必须将任何其他数据类型转换为 VARCHAR 才能完成这项工作。思路如下图：

select
   'header1',
   'header2',
   'header3',
   'header4',
   'header5',
   'header6',
   1 rno
from
   dual
union 
select 
  invoice_type_code,
  shipper_short_code ,
  charge_type_code ,
  incurred_date  , --convert this using to_char if date datatype
  charge_amount  , --convert this using to_char if numeric datatype
  charge_description,
  2 rno
from 
  prepayment_charge_calc
order by rno;

回答by paqogomez

It appears that you want to be able to hardcode various different column names into your query.

您似乎希望能够将各种不同的列名称硬编码到您的查询中。

In Oracle, you can do this with quotes:

在 Oracle 中，您可以使用引号执行此操作：

 select 
  invoice_type_code as "1",
  shipper_short_code as "asdf",
  charge_type_code as "12353",
  incurred_date as "ddf",
  charge_amount as "234$", 
  charge_description as "header6"
 from 
  prepayment_charge_calc 

You can see an example of that here

你可以在这里看到一个例子