Select * from second column ?

  • Hello Experts,

    Actually I have a Dynamically generated table with the columns names like Q1, Q2.... up to any number don't know. I just wanted todo a select * from second column. i.e, avoid the first column any idea? I am using MSSQL 2005.

    Thank you,

    Arshad

  • select * gets expanded to all columns. If you do not want all columns, you must specifically name the columns in the query.

  • Thanks for your prompt reply Champion.

    Actually i created the table through XML and i dont know the number of columns generated by the XML into the table. some time they created the columns from Q1 to Q30. i.e 30 columns and sometime its just Q1 to Q3 i.e. 3 columns only. So i dont know the exact number of columns my temporary table have :(. the number one column is ID of that table. I dont want to * the ID. i just wanted to start with the second column but with Select * option :(.

    Regards

  • You're making contradictory statements. I understand you don't know the column name, but selecting only one column and selecting all columns are conflicting requirements.

    In addition - if your first column is ID, and all other columns are called Q1 through Q30, wouldn't that make column #2 Q1 at all times?

    If it's not that simple - you may need to go to dynamic SQL:

    declare @column_num int

    declare @columnname varchar(100)

    set @column_num=2

    select @columnname=column_name from information_schema.columns

    where table_name='Mytable' and ordinal_position=@column_num

    declare @sql varchar(max)

    set @sql='select ['+@columnname+'] from MyTable'

    Exec(@sql)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Now that I reread that - you want all BUT the first column? You then might need to use what I initially gave you and just build the dynamic SQL to do that.

    What is so bad about including the ID field?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Probably not the best solution as i often think 'like a programmer' but...

    cant you select the whole results into a temp table and then drop the first column then select * from that?

  • you play with "information_schema.columns", based on this you can generate dynamic sql statement.

  • Can I ask why the first column is a problem? Perhaps we have another way to do this.

    Honestly any other way than dealing with this in the client is asking for performance issues under load.

  • Hi All,

    Thank you very much for your replies

    SSChampion Actually i wanted to generate an XML document through stored procedure based on the dynamic Columns and dont need any ID in the XML document. you already know i dont know the number of the columns and names and the columns would be generated in the form of Q1, Q2, Q3........ up to any number. My Query in the stored procedure is

    (Select * from FOR XML PATH('QUESTIONS'), Type) FROM #tmp_DataFeed.

    It displays all the fields from the temp table with ID but i dont want to display that ID.

    Hope that would help you to understand my problem.

    Regards

    Arshad

  • Hi Enthusiastic,

    Thank you for your reply. Actually i dont know the name and number of columns thats why cant able to to create a temp table. If you have any syntax is available to generate or loop through the table for getting the number of columns than please help me? the columns generated like Q1, Q2, Q3 .... up to any number.

    Regards

    Arshad

  • declare @column_num int

    declare @columnname varchar(max)

    declare @tablename varchar(max)

    set @column_num=2

    set @columnname='';

    set @tablename='MyTable'

    select @columnname=@columnname+',['+column_name+']' from information_schema.columns

    where table_name=@tablename and ordinal_position>=@column_num

    declare @sql varchar(max)

    set @sql='select '+substring(@columnname,2,len(@columnname))+' from '+@tablename

    Print (@sql)

    Exec(@sql)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thank you Matt, It works fine just a one more quick Question, how can i make a select statement with your script Actually i wanted to generate an XML document through SQL Query like

    (Select * from FOR XML PATH('QUESTIONS'), Type) FROM @sql where ID = @id.

    Waiting for your reply.

    Arshad

  • Hi Matt,

    Sorry to bother you again. Using same information_Schema.Column how do we filter the record through the same eliminated ID before eliminating the ID? is there any to use the where clause in information_Schema.Column?

    Regards

    Arshad

  • Arshad - remember that you're simply build a SQL command. Build the string so that it returns the query you wish.

    As to using parameters - you probably will want to switch to sp_executeSQL instead of just EXEC, since sp_executeSQL will allow you to pass in parameters.

    I'd suggest this: use the code I gave you to build your string, but instead of EXEC, use PRINT(@SQL) for a while, so that you can get the query code you want.

    Once you have the right code - have a gander at using sp_executeSQL, which is described here:

    http://msdn.microsoft.com/en-us/library/ms188001.aspx

    Give it a shot, and if you get stuck, then post back here.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Here is some code that does what i think you need in the sample database.

    Hope it helps.

    (Please note this uses the old methods to reference the system objects (syscolumns). From SQL 2005 it is recommended to use the newer catalog views. If this is more than a one off process you might want to look into that so that you dont get issues as SQL versions progress...)

    USE [AdventureWorks]

    GO

    -- gather your data into a 'scrap' table

    IF OBJECT_ID(N'temptbl') > 0

    BEGIN

    DROP TABLE temptbl

    END

    SELECT *

    INTO temptbl

    FROM [Person].[Address] AS a

    --DROP TABLE temptbl

    GO

    -- prove its there

    SELECT *

    FROM temptbl ;

    -- show the table id

    DECLARE @TableID INT

    SELECT @TableID = OBJECT_ID(N'temptbl')

    PRINT @tableid

    -- uncomment this TO see the contents OF the syscolumns table

    --SELECT * -- colid

    --FROM [sys].[syscolumns] AS s

    --WHERE s.[id] = @TableID

    --ORDER BY s.[name]

    DECLARE @ColName CHAR(100)

    SELECT @ColName = sc.NAME

    FROM [sys].[syscolumns] AS sc

    WHERE sc.[id] = @TableID

    AND sc.[colid] = 1

    /*

    If the column is a constraint you will need to drop the constraint first

    */

    DECLARE @sql CHAR(100)

    EXEC ( 'ALTER TABLE [temptbl] DROP COLUMN ' + @colname + ';') ;

    GO

    SELECT *

    FROM [temptbl] AS t

Viewing 15 posts - 1 through 15 (of 37 total)

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