CODEWITHSUNDEEP
sql
duckmike
duckmike

Reputation: 1036

Dynamic SQL - combining 2 tables

In my Activities table I have columns - Activity, ActivityBaseTable, FieldName

**Activity**    **ActivityBaseTable**    **FieldName**
Task            TaskBase                 CreatedOn
Task            TaskBase                 ModifiedOn

What I need a sql statement like -

select Activity, ActivityBaseTable, FieldName, MIN([Date field], MAX([Date field])
from Activities

where the min and max date fields would be - 

select min(CreatedOn), Max(CreatedOn)
from TaskBase

or 

select min(ModifiedOn), Max(ModifiedOn)
from TaskBase

Can this be written as one statement?

Upvotes: 0

Views: 69

Answers (1)

Gerrat
Gerrat

Reputation: 29690

You probably won't like this as an answer, but if you can revise your data model to not use a table name in a column, that would be much preferrable. Please see here for a great summary of the topic. I'm cringing when I read that you have dozens of rows with different base ActivityBaseTables. I'm at a loss for a design where this is a good idea.

Depending on your RDBMS, there are lots of examples on this site and others of how to write a stored procedure (which is likely what you'll need) to do this.

I'm just going to plead with you to change the data model & avoid the situation entirely.

Upvotes: 1

Related Questions