ORACLE SQL select between days, show each day in a new column-CodePudding

it is possible in ORACLE SQL, for each day between 2 dates, to get an "own select" with the respective date.

SELECT 
    TO_CHAR('01.09.2022') AS DATE1 
  , TO_CHAR('02.09.2022') AS DATE2
  , TO_CHAR('03.09.2022') AS DATE3
 FROM 
    DUAL

As an example I select from two comboboxes two different dates = 01.09.2022 & 03.09.2022

Now I want to see these dates not in a column with several rows, but each day in a row with several columns.

Background, and maybe it is also a wrong approach. Is to create an "ultragrid" with Infragistics and there I would like to have in the X-AXIS these days with date.

CodePudding user response：

In Oracle SQL (and most other SQL dialects), a SELECT statement must have a fixed, known number of columns that can be determined from the SQL statement; it is not possible to have a dynamic number of columns with a static SQL statement.

Instead, what you should do is generate the dates as rows and then pivot the data in your third-party application that is accessing the database so that the date column becomes the x-axis of your dataset.

You can use something like this SQL:

WITH calendar (day) AS (
  SELECT DATE '2022-09-01'   LEVEL - 1
  FROM DUAL
  CONNECT BY DATE '2022-09-01'   LEVEL - 1 <= DATE '2022-09-03'
)
SELECT  c.day,
        t.green_value,
        t.blue_value,
        t.purple_value
FROM    your_table t
        INNER JOIN calendar c
        ON t.datetime = c.day

CodePudding user response：

Even though your aim how to use that is not clear enough, you can generate such a logic through use of dynamic SQL along with SYS_REFCURSOR by creating a function such as

CREATE OR REPLACE FUNCTION Get_Pivoted_Dates( i_first_date DATE, i_last_date DATE ) RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
BEGIN
  SELECT LISTAGG('date'''||TO_CHAR(i_first_date rn-1,'yyyy-mm-dd')||CHR(39)||' AS date_'||rn ,',') WITHIN GROUP (ORDER BY 0)
    INTO v_sql
    FROM (SELECT level AS rn FROM dual CONNECT BY level <= i_last_date - i_first_date   1 );

  v_sql :='SELECT '|| v_sql ||' FROM dual';

  DBMS_OUTPUT.PUT_LINE(v_sql);
  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

and then call the created function from SQL Developer's console as

SQL> DECLARE
    result SYS_REFCURSOR;
BEGIN
   :result := Get_Pivoted_Dates( date'2022-09-04', date'2022-09-06' );
END;
/

SQL> PRINT result;

DATE_1      DATE_2      DATE_3
---------   ---------   ---------
4.09.2022   5.09.2022   6.09.2022

where three columns are produced as the interval between the dates has three dates in this case.