CODEWITHSUNDEEP
sqloracle-database
y2j
y2j

Reputation: 207

to include a header into result of sQL query

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

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 .

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

can any one suggest me the query for this .

Upvotes: 2

Views: 37985

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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

For example :

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

In any GUI/non GUI based tool, the scroll pane automatically places the table header at the top of the resultset, which is actually the column headers you need.

Upvotes: 1

toddlermenot
toddlermenot

Reputation: 1618

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:

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;

Upvotes: 7

crthompson
crthompson

Reputation: 15865

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:

 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

Upvotes: 2

Related Questions