How to filter some column names based on column names themselve (not value)

Question

How to filter some column names based on column names themselve (not value)

jean-paul.accarie

Say Hey Kid

Points: 695
More actions
December 21, 2009 at 9:26 am

#136868

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jean-paul.accarie Say Hey Kid Points: 695 More actions · Answer 2

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

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

GSquared SSC Guru Points: 260824 More actions · Answer 4

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

jean-paul.accarie Say Hey Kid Points: 695 More actions · Answer 5

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

GSquared SSC Guru Points: 260824 More actions · Answer 6

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