selecting column of type number from table

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 2005 Forums

Transact-SQL (2005)

selecting column of type number from table

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-09-11 : 12:20:10

hello i have this schema

1 2 3
d1 d2 d3
d3 d1 d2

numbers above are column name. what i want to do is to select all data from column 1.

Here's the normal query when you select all data from column 1
select [1] from table

But why is this not working
select '[' + (select column from table2 where column = 1) + ']' from table

the subquery result is [1]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 13:20:04

yuck! What is the big picture here that you're trying to accomplish? that is not a good design.

But I'm curious, what is the structure of table2? is the columnName [column] or [1]?

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-11 : 13:52:42

You can't do it directly like that, you'll have to use dynamic sql.

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-09-11 : 14:10:07

waaaaaaaaaaaaa!
what i'm trying to do here is to select all the data of the column from table1.
here is the sample schema of table1

accountid 1 1desc
1100-01 1100 01

here is sample schema of table2
structurenum structurename
1 name1
2 name2

the reason i did is because i want it to be dynamic. column name is changing.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 14:20:44

please try this:
declare @sql varchar(max)
set @sql='select [' + (select ltrim(rtrim(column)) from table2 where column = 1) + '] from table'
exec(@sql)

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 14:26:41

quote:
the reason i did is because i want it to be dynamic. column name is changing.

As Lamprey says you need to use dynamic sql to refer to dynamic objects. that means constructing a valid sql statement as a string, then executing the string using EXEC or spExecuteSql. If you're still in the design phase of this then I would ask again what the big picture is. There is likely a more better design and we could help with that. Otherwise you're stuck with using dynamic sql.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources