`` I have a table which shows data for each of the dates on a column. The variable date_data is the date where the simulation was done, id and name identify a user, T are all the future dates where values are simulated and value are the random simulated values.

SQL so far:

 select *
    from (select date_data,id, name, scenery, 
                 (extract(month from date)-extract(month from data_date)) 12*(extract(year from date)-extract(year from data_date)) as T, value   
            from escen
            where date_data = '30/09/2022'
              and scenario in ('BASE')
          )    
    pivot ( avg(value) 
            for T between 0 and 120
    order by 1, 2, 3, 4;

This returns a pivoted table with the average value of the 3 different IDs.

Data:

date_data|ID|Name|Scenery|N Simu|Simul|   Date   | Value
30/09/22 |1|  A  |  Base | 1000 |  1  | 30/09/28 | 0,0397
30/09/22 |1|  A  |  Base | 1000 |  2  | 30/09/28 | -0,069

I have different Scenarios and 1000 simulations for each of those. The simul column shows which number of simulation is of these 1000, and I want to get the avg, max, min and stddev of these 1000 simulations for each ID in each date.

Current results:

date_data|ID|Name|Scenery |     30/09/28     |   31/10/28
30/09/22 |1|  A  |  Base  |    avg(value)    |  avg(value)
30/09/22 |2|  B  |  Base  |    avg(value)    |  avg(value)
30/09/22 |3|  C  |  Base  |    avg(value)    |  avg(value)

With the query above, I am able to get the functions, but one by one. I would like to add an extra column that returned which function is showing, and get the 4 results in the same output instead of having 4 different outputs.

Desired results:

date_data|Function|ID|Name|Scenery |     30/09/28     |  31/10/28
30/09/22 |Average |1|  A  |  Base  |    avg(value)    |  avg(value)
30/09/22 |Minimum |1|  A  |  Base  |    min(value)    |  min(value)
30/09/22 |Maximum |1|  A  |  Base  |    max(value)    |  max(value)
30/09/22 |  Stdev |1|  A  |  Base  |    std(value)    |  std(value)
30/09/22 |Average |2|  B  |  Base  |    avg(value)    |  avg(value)
30/09/22 |Minimum |2|  B  |  Base  |    min(value)    |  min(value)
30/09/22 |Maximum |2|  B  |  Base  |    max(value)    |  max(value)
30/09/22 |  Stdev |2|  B  |  Base  |    std(value)    |  std(value)
30/09/22 |Average |3|  C  |  Base  |    avg(value)    |  avg(value)
30/09/22 |Minimum |3|  C  |  Base  |    min(value)    |  min(value)
30/09/22 |Maximum |3|  C  |  Base  |    max(value)    |  max(value)
30/09/22 |  Stdev |3|  C  |  Base  |    std(value)    |  std(value)

CodePudding user response：

You can use:

select *
from   (
  select date_data,
         id,
         name,
         scenario, 
         TRUNC(MONTHS_BETWEEN(SYSDATE, date_data)) as T,
         value   
  from   escen
  where  scenario = 'BASE'
  and    date_data >= ADD_MONTHS(TRUNC(SYSDATE), -120)
)    
pivot (
  avg(value) AS avg,
  min(value) AS min,
  max(value) AS max,
  stddev(value) AS stddev
  for date_data IN (
    DATE '2022-09-30' AS "2022-09-30",
    DATE '2022-10-01' AS "2022-10-01"
  )
)
unpivot (
  value
  for (type, date_data) IN (
    "2022-09-30_AVG"    AS ('AVG',    DATE '2022-09-30'),
    "2022-09-30_MIN"    AS ('MIN',    DATE '2022-09-30'),
    "2022-09-30_MAX"    AS ('MAX',    DATE '2022-09-30'),
    "2022-09-30_STDDEV" AS ('STDDEV', DATE '2022-09-30'),
    "2022-10-01_AVG"    AS ('AVG',    DATE '2022-10-01'),
    "2022-10-01_MIN"    AS ('MIN',    DATE '2022-10-01'),
    "2022-10-01_MAX"    AS ('MAX',    DATE '2022-10-01'),
    "2022-10-01_STDDEV" AS ('STDDEV', DATE '2022-10-01')
  )
);

Which, for the sample data:

CREATE TABLE escen (date_data, id, name, scenario, value) as
  SELECT DATE '2022-09-30', 1, 'A', 'BASE', LEVEL FROM DUAL CONNECT BY LEVEL <= 1000
UNION ALL
  SELECT DATE '2022-09-30', 2, 'A', 'BASE', LEVEL * 2   5 FROM DUAL CONNECT BY LEVEL <= 1000
UNION ALL
  SELECT DATE '2022-10-01', 1, 'A', 'BASE', LEVEL / 2 FROM DUAL CONNECT BY LEVEL <= 1000
UNION ALL
  SELECT DATE '2022-10-01', 2, 'A', 'BASE', LEVEL * 2 - 5 FROM DUAL CONNECT BY LEVEL <= 1000

Outputs:

fiddle