SQL Column Variables?

[Stuh505]

The designers of the database I was hired to work on made the intelligent decision of using the columns as rows...IE, when new data is added it makes a new column like "Year_1", "Year_2" etc (which corresponds like 2000+1, 2000+2).

Now, I need to write a query that gets data for specific years! So for this particular table, I need a way to select the column which has the same name as a string variable which I will make...

Is this possible?? Using MS Access

 

[murph]

The designers of the database I was hired to work on made the intelligent decision of using the columns as rows...IE, when new data is added it makes a new column like "Year_1", "Year_2" etc (which corresponds like 2000+1, 2000+2).

Now, I need to write a query that gets data for specific years! So for this particular table, I need a way to select the column which has the same name as a string variable which I will make...

Is this possible?? Using MS Access

You could do it with a little VBA. Dimension a variable to hold the year and set it to whatever year you want, or create a loop to do multiple years. Then dimension your select statement to be something like this:

"Select " & strYearGoesHere & " from tablename"

strYearGoesHere would be the column name ex. 2004. That would return the field value in the 2004 column for each row.

Hope this makes sense.

Murph

 

[Stuh505]

Thanks Murph, yes that makes sense. So this is impossible in SQL I take it?

 

[murph]

Thanks Murph, yes that makes sense. So this is impossible in SQL I take it?

Well, I'm not exactly sure what else you are trying to accomplish and by what means. It can be done in SQL and Access, but I don't know what results you are trying to achieve. Search for the DatePart function in Access (assuming that is what you are working with) and that should point you in the right direction.

If you can give a little more detail that would be helpful also.

I'm not quite clear on why you can just use

Select 2004 from TableName

and change the 2004 to whatever year you may need ?

 

[Pigeon]

You could do it using dynamic SQL to build your select string then whiling through and running the selects... More details would be helpful. Give us a little example of your table and what you want to the output to look like.

 

[Stuh505]

I am aware of how to use datepart, the problem is that I would come up with a string representation of the name of the column to select, and I do not know how to "dereference" this string to be treated as a syntactic word.

For instance, if I have a table with 2 columns (Col1, Col2)

SELECT "Col1", Col2 FROM tblMain

Obviously, this won't work. I tried using % but that doesn't seem to do it either.

Yes, I could easily change the query manually each time I wanted a new year...but there are other parts of the same query which are dependent of year, and the year is an input paramater to the query. That means that if someone changes the input variable, it will change the year that is selected in certain places...but not this place because it is static.

I am intending to have this query be run by users who are not familiar with Access by clicking buttons on a form for annual federal reporting statistics...so the year will need to be changeable because I won't always be the one running this query.

Using VBA is an acceptable alternative, but I would like to know if it can be done using strictly SQL, because I like my code to be clean and efficient.

 

[murph]

Well, the short answer is, no, you can't do this in strict SQL. (That I know of). The only way I see off the top of my head is to prompt for a year, validate that it is in correct format, then use it in the select clause.