how can i set alias dynamically to all the columns.

Question

table 1

name age id address
ksah 22 43 goa
nora 20 01 vegas
brad 31 32 gakww

as (after query : [select *(using asterisk for all columns) AS "col" from table 1;])

col col col col
ksah 22 43 goa
nora 20 01 vegas
brad 31 32 gakww
or

col1 col2 col3 col4
ksah 22 43 goa
nora 20 01 vegas
brad 31 32 gakww

Please help!!! am new at learning sql,
I have been told to go through Information_SCHEMA, alias, and Cursor, but am not able to understand the way of executing it.
Please suggest something. asap.

Answer 1

Hi @kkmyghty

1. You cannot give multiple columns a same alias like this
   2)To obtain dynamic alias, you need dynamic SQL. Check the following query: create table table1
   (
   name varchar(20),age int,id int,address varchar(20))
   insert into table1 values
   ('ksah',22,43,'goa'),
   ('nora',20,01,'vegas'),
   ('brad',31,32,'gakww') DECLARE @TableName NVARCHAR(100) = N'table1'
   DECLARE @alenzi _Text NVARCHAR(MAX) SELECT COLUMN_NAME,ORDINAL_POSITION
   INTO #COLUMNS
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = @TableName SELECT @alenzi _Text = 'SELECT '
   SELECT @alenzi _Text += ( -- Add in column list, with dynamic column names.
   SELECT COLUMN_NAME +' AS Col'+ CONVERT(VARCHAR,ORDINAL_POSITION)+','
   FROM #COLUMNS FOR XML PATH('')
   )
   SELECT @alenzi _Text = LEFT(@alenzi _Text, LEN(@alenzi _Text) - 1) + ' ' -- Remove trailing comma
   SELECT @alenzi _Text += ' FROM '+@TableName
   --PRINT @alenzi _Text EXEC sp_executesql @alenzi _Text DROP TABLE #COLUMNS,table1

Best regards,
Li Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Suggest what? I'm afraid that I am not understanding your question.

Although,since you are new to SQL, the answer may be; you don't do that. Making column aliases dynamic is not a common thing to do, so it is nothing you learn in SQL 101.