Dynamic column names in oracle 10g sql

Guys, This may be a simple question ..........

Here goes.

I have a sql script that gives me

Emp_no Name Office 1st 2nd 3rd 4th 5th 6th 7th  etc etc.

Is there a way depending on what variable I enter to have the variable appear in the column name.

example

if I typed in "June" as my variable could the column names then be.

Emp_no Name Office 1st June 2nd June 3rd June 4th June 5th June 6th June 7th June  etc etc.

As you can see the Word June is now in the column header. Of course this could be any month in the year.

Regards
DarrenJacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
only possible with dynamic sql where you can specify the column name before running the sql.

v_sql := 'select col1  ' || your_variable ||' from yourtable where ... etc ';
execute immedite(v_sql);
DarrenJacksonAuthor Commented:
is there something I'm missing from this

v_sql := 'select date_from||'&Month' from ifsapp.hrp_abs'
 execute immedite(v_sql)

its keeps saying invalid sql when I run it in plsql developer
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, you put the syntax incorrectly ...

v_sql := 'select date_from ' ||  &Month  || ' from ifsapp.hrp_abs';
execute immedite(v_sql);
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DarrenJacksonAuthor Commented:
I'm sorry about this but when I run it in either plsql developer or sqlplus I get errors

I will attach the errors

I'm sure I am doing something obviously wrong
sqlplus.jpg
plsqldeveloper.jpg
slightwv (䄆 Netminder) Commented:
That is a PL/SQL command.  It needs to be execute inside a PL/SQL block:

declare
   v_sql varchar2(4000);
begin
   v_sql :=  ...
end;
/


That said, I remember a previous question here that needed dynamic column aliases.  I forget the solution and I have yet to locate that question.

I'm still looking...
DarrenJacksonAuthor Commented:
This is my code to stop it from erroring but it doesn't return any data

declare
v_sql varchar2(4000);
&Month varchar2(10);
begin
v_sql := 'select date_from ' ||  &Month  || ' from ifsapp.hrp_abs';
execute immediate(v_sql);
end;

slightw hope you have some luck
slightwv (䄆 Netminder) Commented:
>>but it doesn't return any data

Correct.  You executed a select but don't do anything with the results.

Also, the column headings displayed in sqlplus aren't readily available in PL/SQL.  There is some extra magic needed to get them.
DarrenJacksonAuthor Commented:
ok so if I want to view the contents of the view what do I need to do.

Thanks
slightwv (䄆 Netminder) Commented:
It isn't a view.  It is a PL/SQL block.

You'll need a cursor to loop through the results.

I wouldn't worry about that path since I don't think it will get you what you really need.
DarrenJacksonAuthor Commented:
right ok, thanks for helping
slightwv (䄆 Netminder) Commented:
I couldn't find what I was looking for so I re-created what I think it did.

It's not pretty but it is a sqlplus solution (and SQL Developer when ran as a script).

The main difference is I made the from and to dates variables instead of only having them in the select.

var date_from varchar2(10)
var date_to varchar2(10)

exec :date_from := '06/01/2015'
exec :date_to := '06/30/2015'

clear columns
col day1 new_value day1_head
col day2 new_value day2_head
col day3 new_value day3_head
col day4 new_value day4_head
col day5 new_value day5_head
col day6 new_value day6_head
col day7 new_value day7_head
col day8 new_value day8_head
col day9 new_value day9_head
col day10 new_value day10_head
col day11 new_value day11_head
col day12 new_value day12_head
col day13 new_value day13_head
col day14 new_value day14_head
col day15 new_value day15_head
col day16 new_value day16_head
col day17 new_value day17_head
col day18 new_value day18_head
col day19 new_value day19_head
col day20 new_value day20_head
col day21 new_value day21_head
col day22 new_value day22_head
col day23 new_value day23_head
col day24 new_value day24_head
col day25 new_value day25_head
col day26 new_value day26_head
col day27 new_value day27_head
col day28 new_value day28_head
col day29 new_value day29_head
col day30 new_value day30_head
col day31 new_value day31_head


set term off
select to_char(to_date(:date_from,'MM/DD/YYYY'),'fmMonth DDTH') day1 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+1,'fmMonth DDTH') day2 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+2,'fmMonth DDTH') day3 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+3,'fmMonth DDTH') day4 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+4,'fmMonth DDTH') day5 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+5,'fmMonth DDTH') day6 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+6,'fmMonth DDTH') day7 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+7,'fmMonth DDTH') day8 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+8,'fmMonth DDTH') day9 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+9,'fmMonth DDTH') day10 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+10,'fmMonth DDTH') day11 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+11,'fmMonth DDTH') day12 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+12,'fmMonth DDTH') day13 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+13,'fmMonth DDTH') day14 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+14,'fmMonth DDTH') day15 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+15,'fmMonth DDTH') day16 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+16,'fmMonth DDTH') day17 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+17,'fmMonth DDTH') day18 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+18,'fmMonth DDTH') day19 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+19,'fmMonth DDTH') day20 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+20,'fmMonth DDTH') day21 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+21,'fmMonth DDTH') day22 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+22,'fmMonth DDTH') day23 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+23,'fmMonth DDTH') day24 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+24,'fmMonth DDTH') day25 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+25,'fmMonth DDTH') day26 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+26,'fmMonth DDTH') day27 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+27,'fmMonth DDTH') day28 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+28,'fmMonth DDTH') day29 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+29,'fmMonth DDTH') day30 from dual;
select to_char(to_date(:date_from,'MM/DD/YYYY')+30,'fmMonth DDTH') day31 from dual;

col day1 head "&day1_head" form a12
col day2 head "&day2_head" form a12
col day3 head "&day3_head" form a12
col day4 head "&day4_head" form a12
col day5 head "&day5_head" form a12
col day6 head "&day6_head" form a12
col day7 head "&day7_head" form a12
col day8 head "&day8_head" form a12
col day9 head "&day9_head" form a12
col day10 head "&day10_head" form a12
col day11 head "&day11_head" form a12
col day12 head "&day12_head" form a12
col day13 head "&day13_head" form a12
col day14 head "&day14_head" form a12
col day15 head "&day15_head" form a12
col day16 head "&day16_head" form a12
col day17 head "&day17_head" form a12
col day18 head "&day18_head" form a12
col day19 head "&day19_head" form a12
col day20 head "&day20_head" form a12
col day21 head "&day21_head" form a12
col day22 head "&day22_head" form a12
col day23 head "&day23_head" form a12
col day24 head "&day24_head" form a12
col day25 head "&day25_head" form a12
col day26 head "&day26_head" form a12
col day27 head "&day27_head" form a12
col day28 head "&day28_head" form a12
col day29 head "&day29_head" form a12
col day30 head "&day30_head" form a12
col day31 head "&day31_head" form a12

set term on

select emp_no, 
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 1 then 'off' end) day1,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 2 then 'off' end) day2,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 3 then 'off' end) day3,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 4 then 'off' end) day4,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 5 then 'off' end) day5,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 6 then 'off' end) day6,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 7 then 'off' end) day7,
	--...
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 27 then 'off' end) day27,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 28 then 'off' end) day28,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 29 then 'off' end) day29,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 30 then 'off' end) day30,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 31 then 'off' end) day31
from
	(
	select emp_no,
		holiday
	from (
		select emp_no,
			date_from+COLUMN_VALUE-1 holiday
		from tab1, TABLE(
			cast(
				multiset(
					select level l
					from dual connect by level <= date_to-date_from+1
				) as sys.odciNumberList
			)
		)
	)
	where holiday >= to_date(:date_from,'MM/DD/YYYY') and
		holiday < to_date(:date_to,'MM/DD/YYYY')+1
	) emp_holiday
group by emp_no
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DarrenJacksonAuthor Commented:
slight thanks for this.  is there a way to not have to enter all the days of the month. As this will be ran many times potentially a day it will get a little tedious to enter all the days in manually. Also when you say " SQL Developer when ran as a script" what do you mean by that?

Again thanks for all your help
DarrenJacksonAuthor Commented:
slightw sorry I was to quick on replying I can see it working

This is great thank you very much :)
DarrenJacksonAuthor Commented:
Thankyou again
slightwv (䄆 Netminder) Commented:
Glad to help.

>>is there a way to not have to enter all the days of the month.

No.  You have to know how many columns you have when the SQL is parsed.

>>As this will be ran many times potentially a day it will get a little tedious to enter all the days in manually

Save it as a script.  You can use ACCEPT to have it ask you for the input dates.

Here is the update script:
undefine v_date_from
undefine v_date_to

accept v_date_from char prompt "Enter FROM date: "
accept v_date_to char prompt "Enter FROM date: "

clear columns
col day1 new_value day1_head
col day2 new_value day2_head
col day3 new_value day3_head
col day4 new_value day4_head
col day5 new_value day5_head
col day6 new_value day6_head
col day7 new_value day7_head
col day8 new_value day8_head
col day9 new_value day9_head
col day10 new_value day10_head
col day11 new_value day11_head
col day12 new_value day12_head
col day13 new_value day13_head
col day14 new_value day14_head
col day15 new_value day15_head
col day16 new_value day16_head
col day17 new_value day17_head
col day18 new_value day18_head
col day19 new_value day19_head
col day20 new_value day20_head
col day21 new_value day21_head
col day22 new_value day22_head
col day23 new_value day23_head
col day24 new_value day24_head
col day25 new_value day25_head
col day26 new_value day26_head
col day27 new_value day27_head
col day28 new_value day28_head
col day29 new_value day29_head
col day30 new_value day30_head
col day31 new_value day31_head


set term off
select to_char(to_date('&v_date_from','MM/DD/YYYY'),'fmMonth DDTH') day1 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+1,'fmMonth DDTH') day2 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+2,'fmMonth DDTH') day3 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+3,'fmMonth DDTH') day4 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+4,'fmMonth DDTH') day5 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+5,'fmMonth DDTH') day6 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+6,'fmMonth DDTH') day7 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+7,'fmMonth DDTH') day8 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+8,'fmMonth DDTH') day9 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+9,'fmMonth DDTH') day10 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+10,'fmMonth DDTH') day11 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+11,'fmMonth DDTH') day12 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+12,'fmMonth DDTH') day13 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+13,'fmMonth DDTH') day14 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+14,'fmMonth DDTH') day15 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+15,'fmMonth DDTH') day16 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+16,'fmMonth DDTH') day17 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+17,'fmMonth DDTH') day18 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+18,'fmMonth DDTH') day19 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+19,'fmMonth DDTH') day20 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+20,'fmMonth DDTH') day21 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+21,'fmMonth DDTH') day22 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+22,'fmMonth DDTH') day23 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+23,'fmMonth DDTH') day24 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+24,'fmMonth DDTH') day25 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+25,'fmMonth DDTH') day26 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+26,'fmMonth DDTH') day27 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+27,'fmMonth DDTH') day28 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+28,'fmMonth DDTH') day29 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+29,'fmMonth DDTH') day30 from dual;
select to_char(to_date('&v_date_from','MM/DD/YYYY')+30,'fmMonth DDTH') day31 from dual;

col day1 head "&day1_head" form a12
col day2 head "&day2_head" form a12
col day3 head "&day3_head" form a12
col day4 head "&day4_head" form a12
col day5 head "&day5_head" form a12
col day6 head "&day6_head" form a12
col day7 head "&day7_head" form a12
col day8 head "&day8_head" form a12
col day9 head "&day9_head" form a12
col day10 head "&day10_head" form a12
col day11 head "&day11_head" form a12
col day12 head "&day12_head" form a12
col day13 head "&day13_head" form a12
col day14 head "&day14_head" form a12
col day15 head "&day15_head" form a12
col day16 head "&day16_head" form a12
col day17 head "&day17_head" form a12
col day18 head "&day18_head" form a12
col day19 head "&day19_head" form a12
col day20 head "&day20_head" form a12
col day21 head "&day21_head" form a12
col day22 head "&day22_head" form a12
col day23 head "&day23_head" form a12
col day24 head "&day24_head" form a12
col day25 head "&day25_head" form a12
col day26 head "&day26_head" form a12
col day27 head "&day27_head" form a12
col day28 head "&day28_head" form a12
col day29 head "&day29_head" form a12
col day30 head "&day30_head" form a12
col day31 head "&day31_head" form a12

set term on
set verify off

select emp_no, 
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 1 then 'off' end) day1,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 2 then 'off' end) day2,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 3 then 'off' end) day3,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 4 then 'off' end) day4,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 5 then 'off' end) day5,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 6 then 'off' end) day6,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 7 then 'off' end) day7,
	--...
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 27 then 'off' end) day27,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 28 then 'off' end) day28,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 29 then 'off' end) day29,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 30 then 'off' end) day30,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 31 then 'off' end) day31
from
	(
	select emp_no,
		holiday
	from (
		select emp_no,
			date_from+COLUMN_VALUE-1 holiday
		from tab1, TABLE(
			cast(
				multiset(
					select level l
					from dual connect by level <= date_to-date_from+1
				) as sys.odciNumberList
			)
		)
	)
	where holiday >= to_date('&v_date_from','MM/DD/YYYY') and
		holiday < to_date('&v_date_to','MM/DD/YYYY')+1
	) emp_holiday
group by emp_no
/

set verify on

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.