sql server按数字选择列 [英] sql server select column by number

问题描述

我可以通过 SQL 中的列数选择特定的列吗?类似的东西

Can I select specific columns by the number of the columns in SQL? Something like

SELECT columns(0), columns(3), columns(5), columns(8) FROM TABLE

谢谢

推荐答案

你必须使用动态 SQL 来做到这一点:

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 

这篇关于sql server按数字选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！