December 21, 2009 at 9:26 am
I have a table with many colonnes: basically i add 2 new columns for each year (using predefined column name spelling) for instance:
col_2009_name1, col_2009_name2,
col_2010_name1, col_2010_name2
I'd like to write a SQL request to select the columns matching a given year value
something like
If @Param_Year = 2009
"SELECT col_2009_name1, col_2009_name2
FROM my_table"
If @Param_Year = 2010
"SELECT col_2010_name1, col_2010_name2
FROM my_table"
What is the right technic to do this?
(sorry but I am new with SQL)
Thanks in advance
December 21, 2009 at 9:39 am
You're probably not going to like my advice...
Normalise your table structure. Leave the non-repeating columns in the original table (call it Table1) and move all the repeating columns into a second table (call it Table2) thusly
CREATE TABLE Table2 (
<Table1 primary key> -- foreign key to Table1
EffectiveYear Char(4),
Name1 <data type>,
Name2 <data type>
)
Make the combination of <Table1 primary key> and EffectiveYear the primary key of this second table. Then your query is as simple as this.
SELECT <column list>
FROM Table1 INNER JOIN Table2 ON <join condition>
WHERE Table2.EffectiveYear = @Param_Year
Otherwise you are looking at really nasty dynamic SQL to do this, with all of the side effects and concerns that go along with it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2009 at 8:04 am
Thanks,
Finally I used following solution
If @Param_Year = '2009'
SELECT col_2009_name1 AS name1, col_2009_name2 AS name2
FROM my_table"
else
SELECT col_2010_name1 AS name1, col_2010_name2 AS name2
FROM my_table
regards
December 22, 2009 at 11:04 am
jean-paul.accarie (12/22/2009)
Thanks,Finally I used following solution
If @Param_Year = '2009'
SELECT col_2009_name1 AS name1, col_2009_name2 AS name2
FROM my_table"
else
SELECT col_2010_name1 AS name1, col_2010_name2 AS name2
FROM my_table
regards
That may solve the issue at hand. When 2011 (and subsequent years) comes around, you will need to go back and modify any code where this is done. If at all possible, I would heed Gail's advice and normalize this structure.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2009 at 11:07 am
Change it to rows, not collumns, as already suggested. It'll get nasty over time if you don't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 23, 2009 at 2:31 am
Thanks for your recommendations
Actually main point for me is "how to normalise the table structure" because I cannot decide anything about new columns creation: it is done thru an application (namely Project Server 2007) and those new columns are added within so called "mytable", typically each year new resource fields are added to contain new values for the coming year.
Would it be possible to create temporary table with the structure you propose ?
CREATE TABLE Table2 (
<Table1 primary key> -- foreign key to Table1
EffectiveYear Char(4),
Name1 <data type>,
Name2 <data type>
)
But still the question is: how do I fill in this table for Year '2009, '2010', etc... ?
In my precise example, in "mytable" I have the columns names
col_2009_name1, col_2009_name2, col_2010_name1, col_2010_name2, etc...
If year = '2010' I would have to create a temporary Table2 with
EffectiveYear Char(4), = '2010'
Name1 <data type>, = 'col_2010_name1'
Name2 <data type> = 'col_2010_name2'
I agree that if can avoid to modify requests every year it would be perfect
Thanks in advance for your help
December 23, 2009 at 7:51 am
The best way to handle that, if you really have no say over the table structure, would be to query the sys.columns table, using the object_id of the table you're dealing with, and pick the column name that matches the year you want to query, then use that to build a dynamic SQL query that pulls data from that column.
That or tell whomever designed the table in the first place to learn how to build databases correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy