How to dynamically select columns in Oracle? is this possible?

I have the below data set:

NAME     2012        2013       2014         2015        2016         2017      2018         2019
JOHN      180          185       192          205         199          198       193          null
MIKE      190          191       191          195         195          null       195          195

Currently i have the below query:

SELECT
    GREATEST(COALESCE("2019", 0),
             COALESCE("2018", 0),
             COALESCE("2017", 0),
             COALESCE("2016", 0),
             COALESCE("2015", 0),
             COALESCE("2014", 0),
             COALESCE("2013", 0),
             COALESCE("2012", 0)) AS max_weight
   , LEAST  (COALESCE("2019", 0),
             COALESCE("2018", 0),
             COALESCE("2017", 0),
             COALESCE("2016", 0),
             COALESCE("2015", 0),
             COALESCE("2014", 0),
             COALESCE("2013", 0),
             COALESCE("2012", 0)) AS max_weight
   , PERSON_NAME
  FROM yourTable;

Is there a way to alter te above query so that it asks for the desired year, uses that as an input parameter and only grabs data for that partciular year + the 5 years prior?

So for example: If i inputed 2017 , it would return the following result:

  PERSON_NAME         MIN_WEIGHT           MAX_WEIGHT
   JOHN                  185                  205
   MIKE                  190                  195

Is there any way to do this? (similar to a parameter in a function / stored procedure)? I was thinking of substituting the year #s with some sort of parameter and inputting that …

Thanks in advance.

Answer

You can UNPIVOT your columns into rows, so that you can apply aggregates like MAX on them.

Like so,

with test_data (NAME, "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019" ) AS (
SELECT 'JOHN', 180, 185, 192, 205, 199, 198, 193, null FROM DUAL UNION ALL
SELECT 'MIKE', 190, 191, 191, 195, 195, null, 195, 195 FROM DUAL )
SELECT * 
FROM test_data
UNPIVOT ( 
  weight FOR year IN ("2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019")
);

+------+------+--------+
| NAME | YEAR | WEIGHT |
+------+------+--------+
| JOHN | 2012 |    180 |
| JOHN | 2013 |    185 |
| JOHN | 2014 |    192 |
| JOHN | 2015 |    205 |
| JOHN | 2016 |    199 |
| JOHN | 2017 |    198 |
| JOHN | 2018 |    193 |
| MIKE | 2012 |    190 |
| MIKE | 2013 |    191 |
| MIKE | 2014 |    191 |
| MIKE | 2015 |    195 |
| MIKE | 2016 |    195 |
| MIKE | 2018 |    195 |
| MIKE | 2019 |    195 |
+------+------+--------+

Once you have the data unpivoted like that, you can use aggregate functions and GROUP BY to get the results you’re looking for:

with test_data (NAME, "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019" ) AS (
SELECT 'JOHN', 180, 185, 192, 205, 199, 198, 193, null FROM DUAL UNION ALL
SELECT 'MIKE', 190, 191, 191, 195, 195, null, 195, 195 FROM DUAL )
SELECT name, 
       min(weight) min_weight,
       max(weight) max_weight, 
       max(year) keep ( dense_rank last order by weight desc, year asc ) min_weight_year,
       max(year) keep ( dense_rank first order by weight desc, year asc ) max_weight_year
FROM test_data
UNPIVOT ( 
  weight FOR year IN ("2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019")
)
GROUP BY name;

+------+------------+------------+-----------------+-----------------+
| NAME | MIN_WEIGHT | MAX_WEIGHT | MIN_WEIGHT_YEAR | MAX_WEIGHT_YEAR |
+------+------------+------------+-----------------+-----------------+
| JOHN |        180 |        205 |            2012 |            2015 |
| MIKE |        190 |        195 |            2012 |            2015 |
+------+------------+------------+-----------------+-----------------+