How to change table column name using data from other table

Question

Here are two demo tables:

DECLARE @table1 TABLE( ID int,D_I int,D_O int)
INSERT INTO @table1 VALUES
(1,200,280),
(2,100,300) 

DECLARE @table2 TABLE(column_alia varchar(20),column_name varchar(50))
INSERT INTO @table2 VALUES
('D_I','Daily In'),
('D_O','Daily Out') 

Need output like this:

Should I use PIVOT here?

Answer 1

Hi @Lylyy

Should I use PIVOT here?

Looks like that there are more than two column alias waiting for convert. Then you may consider using Dynamic SQL here. Please check the accepted answer from this similar thread: Select Column as Column names of values in another table in SQL SERVER. Best regards,

Cosmog Hong

Answer 2

You can do a loop a rename the columns like below. However, it is far from certain that this is the best solution to your actual problem. It may be better to return the aliases from the SQL code, and have the client to do the presentational mapping.

The below certainly sorts under the section advanced.

CREATE TABLE  #table1 ( ID int,D_I int,D_O int)
INSERT INTO #table1 VALUES
(1,200,280),
(2,100,300) 

CREATE TABLE #table2(column_alia varchar(20),column_name varchar(50))
INSERT INTO #table2 VALUES
('D_I','Daily In'),
('D_O','Daily Out')

DECLARE @colname nvarchar(500),
        @newname sysname,
        @cur     CURSOR

SET @cur = CURSOR STATIC FOR
   SELECT '#table1.' + column_alia, column_name
   FROM   #table2

OPEN @cur

WHILE 1 = 1
BEGIN 
   FETCH @cur INTO @colname, @newname
   IF @@fetch_status <> 0
      BREAK

   EXEC tempdb..sp_rename @colname, @newname, 'COLUMN'
END

SELECT * FROM #table1
go