sqloraclepivotinfragistics

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


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.

How it should look like


Solution

  • I think I have expressed myself too unclearly. I have 2 comoboxes from which I take the dates and for each days between these days I would like to have produced a separate select.

    I have solved the whole messy in Visual Studio with a subquery

    i thought i had to create a "pivot" for each day? i know even less about this. anyway, the following solution works for now

    string ret = "";
            System.Collections.Generic.List<System.DateTime> allDays = new System.Collections.Generic.List<System.DateTime>();
    
            for (System.DateTime date = this.dtp_DATUM_VON.DateTime; date <= this.dtp_DATUM_BIS.DateTime; date = date.AddDays(1))
                allDays.Add(date);
    
            foreach (var CurrentDate in allDays)
            {
                ret += string.Format("(SELECT TABLENAME.VALUE AS \"{0}\" FROM TABLENAME WHERE GEN_DATUM = TRUNC(TO_DATE('{0}', 'dd.mm.yyyy ')) AND ART = '{1}' AND KEY = '{2}') AS \"{0}\" ,", CurrentDate.ToString("dd.MM.yyyy"), this.tc_Main.ActiveTab.Text, this.str_grd_Selected_Row_Key);
            }
            ret = ret.Remove(ret.Length - 1);
            return ret;
    

    to fill the grid for the statistic display i have a hashtable that passes the value to the database

    SELECT
       TABLENAME.TABLENAME_ID   AS GRD_ROW_ID
    
     , {GET_DAYS_BETWEEN_CMB}
    FROM
       TABLENAME