SQL Server Select Column by Number

sql server select column by number

You have to use dynamic SQL to do this:

DECLARE @strSQL AS nvarchar(MAX)
DECLARE @strColumnName AS nvarchar(255)
DECLARE @iCounter AS integer 
DECLARE @curColumns AS CURSOR 

SET @iCounter = 0
SET @strSQL = N'SELECT '

SET @curColumns = CURSOR FOR 
(
    SELECT * FROM 
    (
        SELECT TOP 99999 
            COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'T_Markers' 
        AND ORDINAL_POSITION < 4 
        ORDER BY ORDINAL_POSITION ASC 
    ) AS tempT 
)

OPEN @curColumns
FETCH NEXT FROM @curColumns INTO @strColumnName 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @strColumnName 
    IF @iCounter = 0 
        SET @strSQL = @strSQL + N'
     [' + @strColumnName + N'] ' 
    ELSE 
        SET @strSQL = @strSQL + N'
    ,[' + @strColumnName + N'] ' 
    SET @iCounter = @iCounter + 1 
FETCH NEXT FROM @curColumns INTO @strColumnName 
END
CLOSE @curColumns
DEALLOCATE @curColumns 

SET @strSQL = @strSQL + N' 
FROM T_Markers 
'

PRINT @strSQL 

How can I get column names from a table in SQL Server?

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

Selecting column by its number

This is not exactly same as what you are trying to do. However, It is almost there. It won't select column by number, however you dont have to specify the explicit column from your real table while writing this query.

As all us suggested, you have to use the dynamic SQL. This is a little idea I created:

create table test1(name1 varchar(10), address1 varchar(10), zipcode1 varchar(10))

insert into test1 values('Test1.1','USA','12344')
insert into test1 values('Test1.2','USA','12344')
insert into test1 values('Test1.3','USA','12344')
insert into test1 values('Test1.4','USA','12344')

create table test2(name2 varchar(10), address2 varchar(10), zipcode2 varchar(10))

insert into test2 values('Test2.1','USA','12344')
insert into test2 values('Test2.2','USA','12344')
insert into test2 values('Test2.3','USA','12344')
insert into test2 values('Test2.4','USA','12344')

You see, the Table name, and the Column name are completely different in both.
Now this sql statement doesn't care about column names :

select * from
(
    select '' as T1, '' as T2, '' as T3
    union all
    select * from test1   --No matter whether it is Id, Name or description
    union all
    select * from test2   --No matter whether it is Id, Name or description
) as D
where D.T1<>''  -- your other conditions!

Only issue is, since we are using Union, you have to match the number of columns when you specify your empty columns:

select '' as T1, '' as T2, '' as T3, '' as T4, 0 as T5 -- and so on

Here's the output:

SQL query for column numbers

You miss the partition

OVER(PARTITION BY OrderNumber ORDER BY Page)

MS SQL Server how to instantly insert a 1 to 10 number column to table (virtual column)?

You can use a CROSS JOIN in concert with an ad-hoc tally table

Example

Select A.*
      ,B.Code
 From  YourTable A
 Cross Join ( Select Top 10 Code=row_number() Over (Order By (Select NULL)) From master..spt_values n1 ) B

---

Related Topics