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

Transact-SQL (2005)

Column selection

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-03-10 : 11:09:10

Hi i have a table which has 15 columns.I want to write a query hich selects only the first 14 columns in the table.Last column is not needed in that query.We need to write select column m\names,but is there any way to write a query which can be used instead of specifying the column name.
like for rown we put select top 14 or set rowcount 14;in the same way is ther any option for the coulmn also

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-10 : 11:10:06

No. Not without using dynamic SQL.

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-10 : 11:12:07

It would be better practice to name the columns incase columns are added or deleted in the future - which could cause unexpected results or errors.

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-03-10 : 11:49:38

how wwe can do it using dynamic sql

susan

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-10 : 12:33:20

This will give you problems in the future if columns are added or deleted:

declare @N int
declare @S varchar (1000)
declare @D varchar(250)
set @D='aaa' --tablename
--Get Number of columns minus one
set @N =
(
Select count (column_id)-1 from sys.columns where object_id=
(
Select object_id from sys.objects
where
name=@D)

)
set @s = 'Select top ' + Convert(varchar,@N) + ' name from sys.columns where object_id=
(
Select object_id from sys.objects
where
name=''' + @D +''')
order by column_id desc '
print @s
exec (@S)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-11 : 02:46:33

quote:
Originally posted by susan_151615

Hi i have a table which has 15 columns.I want to write a query hich selects only the first 14 columns in the table.Last column is not needed in that query.We need to write select column m\names,but is there any way to write a query which can be used instead of specifying the column name.
like for rown we put select top 14 or set rowcount 14;in the same way is ther any option for the coulmn also

susan

Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53384

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources