none
Get top 5 collums RRS feed

  • Question

  • I have a table with 100 collums

    i need to get only first 50 collums

     

    is there any way other than hardcoding the 50 collum names in the select statement

    Wednesday, October 13, 2010 9:22 AM

Answers

  • 1. Specify those columns explicity

    2

    .WITH cte
    AS
    (
    SELECT *,ROW_NUMBER() OVER (ORDER BY colid) rn FROM sys.syscolumns
    WHERE id=object_id('Person.Contact')
    ) SELECT 'SELECT '+STUFF(
      (SELECT ',' + name as [text()] FROM cte WHERE rn<=2 ORDER BY rn for xml path('')),
      1, 1, '')+' FROM Person.Contact';


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 13, 2010 9:36 AM
    Answerer
  • Please use this procedure to get the first 50 column 
    Declare @query varchar(max)
    declare @columnname varchar(150)
    declare @i int 
    set @i = 1
    SET @query = ''
    while @i <= 10
    begin
    SET @columnname = ''
    select @columnname = name from sys.columns where object_id = ( select object_id from sys.tables 
    where type = 'U' and name = 'tablename' )
    and column_id = @i
    print @columnname
    IF @columnname <> '' 
    begin
    SET @query = @query + @columnname + ','
    end
    SET @i = @i + 1
    end
    SET @query = substring(@query,0,len(@query))
    EXEC('SELECT ' + @query + ' FROM tablename')

     


    " Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
    Click here to read my blog
    • Marked as answer by VarunKumar17 Wednesday, October 13, 2010 10:44 AM
    Wednesday, October 13, 2010 9:48 AM

All replies

  • 2 solutions 

    either write dynamic sql .. pick up ur select list from syscolumns... 

    or exec sp_help yourtablename.. 

    copy the 50 coulmn names [Hard code as u said ...] in your select statement quesry 


    Hope that helps ... Kunal
    Wednesday, October 13, 2010 9:32 AM
  • 1. Specify those columns explicity

    2

    .WITH cte
    AS
    (
    SELECT *,ROW_NUMBER() OVER (ORDER BY colid) rn FROM sys.syscolumns
    WHERE id=object_id('Person.Contact')
    ) SELECT 'SELECT '+STUFF(
      (SELECT ',' + name as [text()] FROM cte WHERE rn<=2 ORDER BY rn for xml path('')),
      1, 1, '')+' FROM Person.Contact';


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 13, 2010 9:36 AM
    Answerer
  • Please use this procedure to get the first 50 column 
    Declare @query varchar(max)
    declare @columnname varchar(150)
    declare @i int 
    set @i = 1
    SET @query = ''
    while @i <= 10
    begin
    SET @columnname = ''
    select @columnname = name from sys.columns where object_id = ( select object_id from sys.tables 
    where type = 'U' and name = 'tablename' )
    and column_id = @i
    print @columnname
    IF @columnname <> '' 
    begin
    SET @query = @query + @columnname + ','
    end
    SET @i = @i + 1
    end
    SET @query = substring(@query,0,len(@query))
    EXEC('SELECT ' + @query + ' FROM tablename')

     


    " Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
    Click here to read my blog
    • Marked as answer by VarunKumar17 Wednesday, October 13, 2010 10:44 AM
    Wednesday, October 13, 2010 9:48 AM
  • It would be much better to create a VIEW against the top 50 columns of that table. Use the view in your procedures and queries instead of actual table.

     


    Please visit my Blog for some easy and often used t-sql scripts
    Wednesday, October 13, 2010 10:04 AM
  • I dont want to do hardcoding thats why i mentioned
    Wednesday, October 13, 2010 10:45 AM
  • You are still hardcoding number of columns in both scripts.

    If you mean it will be difficult for you, writing VIEW would have been a one time hassel.

    Anyways, your choice.


    Please visit my Blog for some easy and often used t-sql scripts
    Wednesday, October 13, 2010 11:04 AM