CODEWITHSUNDEEP
sql-servert-sql
Diogo Santos
Diogo Santos

Reputation: 830

Sequential Column names

Having a simple query such as:

select * from foo

Where the output has the columns

bar | abc | def
---------------
...............

It is possible to encapsulate this query into another one and give sequential names instead to the columns such as 1|2|3... (mantaining all the rows intact)?

I know this sounds weird and probably it isn't even possible.

Thanks for the help!

Upvotes: 1

Views: 794

Answers (2)

Nițu Alexandru
Nițu Alexandru

Reputation: 714

Try this:

DECLARE @TableName NVARCHAR(100) = 'YOUR TABLENAME HERE',
    @Query NVARCHAR(MAX) = 'SELECT '

SELECT @Query = CONCAT(@Query, '    ', COLUMN_NAME, ' AS ', QUOTENAME(ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION)), ', 
')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName

SET @Query = SUBSTRING(@Query, 0, LEN(@Query) - 3) + ' 
FROM ' + QUOTENAME(@TableName)

You use INFORMATION_SCHEMA.COLUMNS to get all our table's columns, ROW_NUMBER() window function to generate sequential number for column aliases and sp_ExecuteSQL to execute dynamic generated queries.

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Try This 

DECLARE @Sql NVARCHAR(max),
        @TableName VARCHAR(100) = '<YourTableName>'
;WITH CTE
AS
(
SELECT  DENSE_RANK()OVER (ORDER BY COLUMN_NAME) Rno,
        TABLE_NAME,
        COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_CATALOG='Database' 
AND TABLE_NAME =  @TableName
) 
SELECT 'SELECT '+ STUFF((SELECT ', '+ COLUMN_NAME +' AS '+ QUOTENAME(CAST(Rno AS VARCHAR))
FROM CTE
FOR XML PATH ('')),1,1,'') + ' FROM '+@TableName 
PRINT @Sql
EXEC (@Sql)

Upvotes: 1

Related Questions