how can i set alias dynamically to all the columns.

kkmyghty 41 Reputation points
2022-10-28T11:56:25.213+00:00

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.

Azure SQL Edge
Azure SQL Edge
An Azure service that provides a small-footprint, edge-optimized data engine with built-in artificial intelligence. Previously known as Azure SQL Database Edge.
47 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,853 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,495 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,584 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 24,931 Reputation points
    2022-10-31T07:38:52.22+00:00

    Hi @kkmyghty

    1. You cannot give multiple columns a same alias like this 255478-image.png
      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.


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-10-28T21:51:13.907+00:00

    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.

    0 comments No comments