Dynamically select column - Oracle SQL query

Question

This question might seem completely idiotic but here it goes.

Is it possible to specify/use dynamic column names in sql query.

For e.g. Let’s say Table has following columns.

Column: "1",  "2", "3", "4", "5"

Then for given value of X (coming from DATEDIFF function) let’s say 3 – I want to get sum of values in columns from “1” to “3”. For X = 4, sum of values in columns from “1” to “4” and so on…

Actual Problem:

This table is basically for tracking how many times a particular user logged in past X days. X is defined. We update the record only when user actually logs in. So to be able to calculate correct value at any point of time, I thought of this schema.

"User", "LastLoginDate", "1", "2", "3", "4", "5",

"1" - represents number of times user logged in on 1 day before `LastLoginDate`
"2" - represents number of times user logged in on 2 day before `LastLoginDate`
 and so on..

Now to calculate value on LastLoginDate + 2 days - I just sum values in columns "1", "2", "3". Values in columns "4" and "5" are stale because 2 days have passed.

I hope I was able to explain question correctly.

Jitendra

That's a denormalized table design. There isn't a standard (nor usually a non-standard) way to do what you wish to do. You should specify which DBMS you're working with; there might be a trick specific to that DBMS that works. — Jonathan Leffler
– Jonathan Leffler, Commented Mar 6, 2014 at 0:17

mdahlman · Accepted Answer · 2014-03-06 00:43:18Z

3

I think you'll be a lot happier with a table like this:

create table login_counts (
   user       varchar2(30)
  ,the_date   date
  ,num_logins integer
);

Each time a user logs in you can increment the relevant row. (You can delete old rows at the same time if you want.) Selecting the sum of logins during a specified time period like "today - 2 days" or "last_login - 2 days" is simple with this structure.

answered Mar 6, 2014 at 0:43

mdahlman

9,4104 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BWStearns Over a year ago

Another nice thing about this is that you could change your mind about record keeping without having to mess with the database. If you suddenly want to keep records for 500 days you don't have to make a 500 column table (please don't do that).

RandomQuestion Over a year ago

That looks good. Seems I got carried away in my thought process of adding columns in existing table.

Collectives™ on Stack Overflow

Dynamically select column - Oracle SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related