0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
srikanth
  • 159
  • 3
  • 14
  • Possible duplicate of: http://stackoverflow.com/questions/368505/is-it-possible-to-select-sql-server-data-using-column-ordinal-position – James Johnson Sep 12 '11 at 20:03

5 Answers5

2

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

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

This is not possible without writing all names.

Icarus
  • 63,293
  • 14
  • 100
  • 115
1

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

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)
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • voting this down is a bit harsh as the result of this is: select n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20,n21,n22,n23,n24,n25,n26,n27,n28,n29 from – t-clausen.dk Sep 13 '11 at 07:10
  • 1
    Dynamic SQL is the only solution to the question. I'd argue that "not possible" would be an incorrect answer. "Not preferable" would be correct. – SQLMason Sep 16 '11 at 20:48
0

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)
SQLMason
  • 3,275
  • 1
  • 30
  • 40
  • Using dynamic sql to save a couple of minutes of typing is a bad choice. – HLGEM Sep 12 '11 at 21:04
  • 1
    @HLGEM - That is hardly the issue here, question was can it be done. Answer is: Only with with dynamic sql, and this is how it could be done. In extreme cases it will speed up the sql alot to choose fewer columns. +1 – t-clausen.dk Sep 13 '11 at 07:21
  • 2
    It is the only solution. I think that it's a bad reason to -1 me. – SQLMason Sep 16 '11 at 20:46
  • I don't disagree with HLGEM, although, the dynamic SQL solution would handle if a new column was added. Since they have column names such as N2...N100, I suggest that they insert columns frequently. – SQLMason Sep 16 '11 at 20:50