locked
Select * from Second Column? RRS feed

  • Question

  •  

    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

    Friday, October 10, 2008 9:07 PM

Answers

  • You can do SELECT Q2  if you know the column name,

    Otherwise you have to do dynamic SQL  Column names in information_schema (columns)  view.

    Dynamic SQL article:

    http://www.sqlusa.com/bestpractices/dynamicsql/ 

     


    Friday, October 10, 2008 9:15 PM
    Answerer
  • Hi:

     

      Give you a example:

     

    Code Snippet

     

    USE tempdb
    GO

    CREATE TABLE test1
    (
     
    q1 INT,
     
    q2 INT,
     
    q3 INT,
     
    q4 INT
    )

    CREATE TABLE test2
    (
     
    c1 INT,
     
    c3 INT,
     
    c2 INT,
     
    c4 INT
    )
    GO

    DECLARE @s VARCHAR(1000)
    DECLARE @table_name VARCHAR(100)


    SET @table_name 'test1'  --or test2

    SELECT
     
    @s 'select ' name ' from ' @table_name
    FROM sys.columns
    WHERE OBJECT_ID OBJECT_ID(@table_name)
     AND 
    RIGHT(name,12

    EXEC(@s)
    GO

    DROP TABLE test1
    DROP TABLE test2
    GO

     

     

    Hope it helps.

    Saturday, October 11, 2008 2:22 AM

All replies

  • You can do SELECT Q2  if you know the column name,

    Otherwise you have to do dynamic SQL  Column names in information_schema (columns)  view.

    Dynamic SQL article:

    http://www.sqlusa.com/bestpractices/dynamicsql/ 

     


    Friday, October 10, 2008 9:15 PM
    Answerer
  • Hi:

     

      Give you a example:

     

    Code Snippet

     

    USE tempdb
    GO

    CREATE TABLE test1
    (
     
    q1 INT,
     
    q2 INT,
     
    q3 INT,
     
    q4 INT
    )

    CREATE TABLE test2
    (
     
    c1 INT,
     
    c3 INT,
     
    c2 INT,
     
    c4 INT
    )
    GO

    DECLARE @s VARCHAR(1000)
    DECLARE @table_name VARCHAR(100)


    SET @table_name 'test1'  --or test2

    SELECT
     
    @s 'select ' name ' from ' @table_name
    FROM sys.columns
    WHERE OBJECT_ID OBJECT_ID(@table_name)
     AND 
    RIGHT(name,12

    EXEC(@s)
    GO

    DROP TABLE test1
    DROP TABLE test2
    GO

     

     

    Hope it helps.

    Saturday, October 11, 2008 2:22 AM