Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Shishir.

Asked: September 13, 2017 - 5:54 am UTC

Last updated: September 13, 2017 - 5:01 pm UTC

Version: Oracle 11g DB 11.2.0.4.0

Viewed 1000+ times

You Asked

Is there a way to write a SQL which selects the number of columns based on the data available ..

Say i am trying to post the number of lines inserted on a table per hour on a day ..

So when i run the select between 03:00-04:00 hr it should give me 4 columns only , when i run the same between 13:00 - 14:00 ... it should give me 14 columns

I was thinking of using PIVOT .. not sure if it can be dona via that

and Chris said...

Sounds like you need dynamic SQL!

Create your statement based on the time or whatever. Then run it with execute immediate or dbms_sql:

declare
  sql_stmt     varchar2(1000);
  current_hour pls_integer;
begin
  current_hour := extract(hour from systimestamp);
  
  if current_hour between 3 and 4 then 
    sql_stmt := 'select col1, col2 from t';
  elsif current_hour between 5 and 6 then 
    sql_stmt := 'select col1, col2, col3, col4 from t';
  elsif ... 
    sql_stmt := ...
  end if;
  
  --run dynamic SQL with execute immediate/DBMS_SQL
end;
/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

One column per hour?

Stew Ashton, September 13, 2017 - 3:39 pm UTC

I think the questioner wants one column per hour in the day so far. For example:
drop table t purge;
create table t as
select trunc(sysdate) + (level-1)/48 dte, 
  level num
from dual 
connect by level <= 8;

declare
  l_sqltext varchar2(4000) :=
'select * from (
  select extract(hour from to_timestamp(dte)) hr, num
  from t
  where dte >= trunc(sysdate)
)
pivot(sum(num) for hr in(#PIVOTLIST#))';
  l_pivotlist varchar2(4000);
begin
  select listagg((level-1) || ' as "'||to_char(level-1,'fm09')||':00-'||to_char(level,'fm09')||':00"', ',')
    within group(order by level)
    into l_pivotlist
  from dual
  connect by level <= (
    select count(distinct extract(hour from to_timestamp(dte)))
    from t
    where dte >= trunc(sysdate)
  );
  l_sqltext := replace(l_sqltext, '#PIVOTLIST#', l_pivotlist);
  dbms_output.put_line(l_sqltext);
end;
/
select * from (
  select extract(hour from to_timestamp(dte)) hr, num
  from t
  where dte >= trunc(sysdate)
)
pivot(sum(num) for hr in(0 as "00:00-01:00",1 as "01:00-02:00",2 as "02:00-03:00",3 as "03:00-04:00"));


00:00-01:00 01:00-02:00 02:00-03:00 03:00-04:00
----------- ----------- ----------- -----------
          3           7          11          15
Best regards, Stew
Chris Saxon
September 13, 2017 - 5:01 pm UTC

That's some nice SQL.

Now it's down to the questioner to clarify their requirements :)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library