CODEWITHSUNDEEP
sqlsql-serverdatabase
Liza
Liza

Reputation: 21

Rename table columns by mapping them to other table's column values

I have 2 tables:

Table A:

Perspectiveid   ColumnOrder   UIDisplayName 
-------------------------------------------
   213              1           Alpha
   213              2           Beta
   213              3           Gamma

Table B: columns Id, Col1, Col2, Col3

I want the final result as in Table B as : 

(`Id, Alpha, Beta, Gamma`)

i.e, Col1 in table B corresponds to ColumnOrder 1 in table A and should be renamed with the UIDisplayName[Alpha] of ColumnOrder 1

Upvotes: 1

Views: 501

Answers (2)

DarkRob
DarkRob

Reputation: 3833

You can use pivot to convert row into columns. You may use this link for more info on pivot link
For your query :-

 ; with cte as (
 select Perspectiveid,cast(ColumnOrder as varchar(max)) as ColumnOrder,UIDisplayName from tableA)
 select * into temptable from (
 select Perspectiveid,ColumnOrder,UIDisplayName from cte
 ) as d
 pivot (
 max(ColumnOrder) for UIDisplayName in ( [Alpha], [Beta], [Gamma] )
 ) as P

This will give you result as

make this as temptable

   Perspectiveid   | [Alpha] | [Beta]  | [Gamma]
       213             1          2        3

After that delete from temptable 

   delete from temptable

Now insert data from yourtable to this temptable as

  insert into temptable ( Perspectiveid , [Alpha], [beta], [gamma] )
  select id, col1, col2, col3 from #tableb

After that drop your tableB then rename your temptable to tableB

  Drop table #tableB

  GO
  select * into #tableB from temptable


  GO

  Drop table temptable

OR

if you are want to change each column individually then try this

 select ' exec sp_rename ''tableb.col' + cast(columnorder as varchar(5)) + ''', ''' + UIDisplayName + ''', ''COLUMN'''  from tableA

this will give you the query to execute and change column name individually.

Upvotes: 1

tezzo
tezzo

Reputation: 11105

You can use a CURSOR to execute a dynamic sp_rename.

DECLARE @ColumnOrder AS INTEGER
DECLARE @UIDisplayName AS VARCHAR(32)

DECLARE C_ColumnTitle CURSOR FOR
    SELECT Table_A.ColumnOrder,
        Table_A.UIDisplayName
    FROM Table_A

OPEN C_ColumnTitle
FETCH NEXT FROM C_ColumnTitle INTO @ColumnOrder, @UIDisplayName

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @SQLString AS VARCHAR(MAX)
    SELECT @SQLString = 'EXECUTE sp_rename ''Table_B.Col' + CAST(@ColumnOrder AS VARCHAR(32)) + ''', ''' + @UIDisplayName + ''', ''COLUMN'';'

    EXEC(@SQLString)

    FETCH NEXT FROM C_ColumnTitle INTO @ColumnOrder, @UIDisplayName

END

CLOSE C_ColumnTitle
DEALLOCATE C_ColumnTitle

Upvotes: 0

Related Questions