Dynamic field select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic field select

Author

Topic

dbutler_05
Starting Member

17 Posts

Posted - 2006-06-21 : 16:17:09

I have a table I'm trying to pull a single column for. The table has 12 columns named month1, month2,... month 12. I want to do something where I select only the column for the current month.

Something along the lines of:

Select MonthX from Table A.

X = variable defining Month #. Seems like it should be easy, but...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 16:24:43

First, your table design is not normalized which is why you require to do this dynamically. You should consider normalizing your database.

Normalization:
http://www.datamodel.org/NormalizationRules.html

Second, here's the dynamic SQL article:
http://www.sqlteam.com/item.asp?ItemID=4599

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-22 : 04:58:51

Also refer
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 05:17:24

[code]-- prepare test data
declare @test table
(
f1 varchar(50), f2 varchar(50), f3 varchar(50),
f4 varchar(50), f5 varchar(50), f6 varchar(50),
f7 varchar(50), f8 varchar(50), f9 varchar(50),
f10 varchar(50), f11 varchar(50), f12 varchar(50)
)

insert @test
select 'This', 'is', 'not',
'normalized', 'but', 'for',
'some', 'reason', 'this',
'works', 'great', 'anyway' union all
select 'Also', 'there', 'is',
'really', 'no', 'need',
'for', 'dynamically', 'built',
'sql', 'query','here'

declare @WantedColumn tinyint

select @WantedColumn = 11

-- show all data
select * from @test

-- Do the work
SELECT @WantedColumn AS 'Wanted column',
CASE
WHEN @WantedColumn = 1 THEN f1
WHEN @WantedColumn = 2 THEN f2
WHEN @WantedColumn = 3 THEN f3
WHEN @WantedColumn = 4 THEN f4
WHEN @WantedColumn = 5 THEN f5
WHEN @WantedColumn = 6 THEN f6
WHEN @WantedColumn = 7 THEN f7
WHEN @WantedColumn = 8 THEN f8
WHEN @WantedColumn = 9 THEN f9
WHEN @WantedColumn = 10 THEN f10
WHEN @WantedColumn = 11 THEN f11
WHEN @WantedColumn = 12 THEN f12
ELSE NULL -- Just in case the wanted column (month) falls out of range
END 'Wanted data'
FROM @test[/code]
Outputs[code]
f1 f1 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12
---- ----- --- ---------- --- ---- ---- ----------- ----- ----- ----- ------
This is not normalized but for some reason this works great anyway
Also there is really no need for dynamically built sql query here

Wanted column Wanted data
------------- -----------
11 great
11 query[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 05:21:49

quote:
Originally posted by tkizer

First, your table design is not normalized which is why you require to do this dynamically. You should consider normalizing your database.

Yes, I agree with you that the table need to be normalized.
But however, we do not know if dbutler_05 has the time or even the knowledge to do this. Nor even how this would impact other systems involved. Normalization is important, no argue there.

There is no need for Dynamic SQL, see my solution that work in his current environment.

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-22 : 11:42:31

Quite possibly the most awesome query ever written. I love it.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-06-23 : 02:59:31

Hehe, quite cool...not really rocket science but still quite cool. Go sweden! (I just bought myself a swedish football-jersey to support our brothers in the WC...and to all you americans; yes it's called FOOTBALL! hehe)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources