How to Retrieve the columns...

  • Hi,

     

         Here is my query ,

         I have a table with the 100 columns.Now i want to display only 99 columns through the select statemnet.Can any one  give me a query to this.

     

     

    Thanks,

    Gaddan.

  • You have to list all the columns. You can just type them out, but I'm guessing it's that you want to avoid.

    There are various ways to save your typing. Maybe the most straightforward is to right-click on the table in the object browser in query analyser and do 'script object to new window as -> select'.

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • set nocount on

    Declare @TableName varchar(128), @ColumnList varchar(1000)

    Declare @SQL varchar(1000)

    set @TableName = 'your Table'

    SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name

    FROM INFORMATION_SCHEMA.Columns

    WHERE Ordinal_position <= 95

    and table_name = @TableName

    Set @SQL = 'Select ' + @ColumnList + ' from ' + @TableName

  • Thanks for the reply...

       But i did not explain my view clearly in that....

        I want to display 99 columns with data.i think now u got wot my query is?

     

     

     

    Thanks,

  • For ex:-

    I have a table with 3 columns as below

    select * from emp

    no name sal

    2   Joe    2000.00

    3    Mary   1000.00

    4    Julie   5000.00

    2    Joe    1000.00

    1     Ram   1000.00

    Now my question i want to display only two columns with data.and one more thing is here the columns are only two but i have to use this one as dynomic purpose,instead of writing all col names.

    no  name

    2   Joe

    3   Mary

    4   Julie

    2   Joe

    1   Ram.

     

  • Since you want to use this dynamically is it safe to assume that the table names will also be dynamic and that there is more than one table you'll be querying this way ?!?!

    Also, when you say you want to return 99 columns out of 100 which is the one you want to eliminate ?! For example, in any given table, would it be the last column (ordinal position) that you don't want to include in your query ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Thanks,

    Ya You are right...that would be the last column that i dont want to include in my quuery....

    Table name will not be dynomic,columns will be dynomic.In that i want to exclude the last col.

    Thant means i want to select all the columns except the last column.Got it?

     

     

    Regards,

    Gaddan.

     

  • Just curios but why do you not want the last column? Is it a 'text' field? I ask because if it is a smallish field you could just retrieve it anyway even if your not going to use it in your app.

    If it is a large column maybe you should split it off into its own table anyway. This would solve your immediate problem and probably give you better performance on other operations.

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • It is an 'integer' field and i want to use this type of query in reports that would not display the last columns.

    This is the one small task i have to do.Is there any easy way to do like that.

     

    Thanks,

     

     

     

     

     

     

  • It it's just one table then I can't understand why you don't just type out the column names...

    At any rate, Venkat has already posted the solution for you - the only addition you would have to make is to "exec" the sql..

    DECLARE @strColNames VarChar(500)
    DECLARE @tblName VarChar(50)
    
    SET @tblName = 'myTable'
    
    
    SELECT @strColNames = ISNULL(@strColNames +', ', '') + name FROM syscolumns where id = object_id(@tblName)
    AND colorder NOT IN (SELECT MAX(colorder) FROM syscolumns where id = object_id(@tblName))
    PRINT @strColNames
    
    EXEC('SELECT ' + @strColNames + ' FROM ' + @tblName)
    







    **ASCII stupid question, get a stupid ANSI !!!**

  • Just realized that Venkat's using information_schema views which is a much better option:

    change your select to get your column names from this instead..

    DECLARE @strColNames VarChar(500)
    DECLARE @tblName VarChar(50)
    
    SET @tblName = 'myTable'
    
    
    SELECT @strColNames = ISNULL(@strColNames +', ', '') + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tblName AND ORDINAL_POSITION < (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tblName)
    
    --PRINT @strColNames
    
    EXEC('SELECT ' + @strColNames + ' FROM ' + @tblName)
    







    **ASCII stupid question, get a stupid ANSI !!!**

  • Hey Thanks,

       Thats what i want.That is only one table and the columns are dynomic.After creating the table i am adding one more column(generatedid) for sequence numbers but i dont want to display that column while retreiving the data.That is my requirement.

     

     

    Thanks once again...

     

     

    Regards,

    Gaddan.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply