CODEWITHSUNDEEP
sql-servert-sql
srikanth
srikanth

Reputation: 159

SQL Server - select columns from given range

I have a table where I have columns like below

[Index], [Length],[N1],[N2]....[N99]

Now, is possible to select only [N2]] ... [N29] columns without writing all names.

Upvotes: 0

Views: 9713

Answers (5)

HLGEM
HLGEM

Reputation: 96552

You can of course drag and drop all the columns from the object browser and then delete the ones you don't want. At least that way you don;t have any typos.

I would be concerned about the design of a table with that many columns. Espceially if they really are N1-N99. You may need a redesign to a related table. Also wide tables can cause performance issues.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

Using dynamic sql is the closest you can get to not writing the columns. Here is an example:

declare @sql varchar(max)

select @sql = coalesce(@sql+',', 'select ') + 'n' + cast(number as varchar(2))
from master..spt_values as N 
where type = 'P' and 
number between 2 and 29 

set @sql = @sql + ' from <yourtable>'

--select @sql
exec (@sql)

Upvotes: 0

SQLMason
SQLMason

Reputation: 3275

How about this:

DECLARE @columns VARCHAR(MAX), 
        @tablename VARCHAR(255), 
        @from VARCHAR(255), 
        @select VARCHAR(100)

SET @tablename = 'orderheader'

SELECT @columns = STUFF(
(
    SELECT ',[' + column_name + ']'
    FROM information_schema.columns
    WHERE table_name = @tablename
    AND NOT column_name IN ('N2', 'Index', 'Length')
    FOR XML PATH('')
),1, 1, '')

SELECT @select = 'SELECT ', @from = ' from ' + @tablename

EXEC(@select + @columns + @from)

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

No, it's not possible. You need to explicitly list the subset of columns you want to return.

Upvotes: 2

Icarus
Icarus

Reputation: 63956

This is not possible without writing all names.

Upvotes: 1

Related Questions