Change Columns Name Dynamically In Sql

I have a query result like this : Date User1 User2 User3 .... ---------------------------------- 1/1/2000 55 78 98 ... 1/1/2001 26 33 56 ... 1/1/2002

Solution 1:

SQL Fiddle

Schema Setup:

createtable history (date datetime, name varchar(10), valueint);
insert history values
 ('20130101', 'user1', 123),
 ('20130101', 'user2', 124),
 ('20130101', 'user3', 125),
 ('20130102', 'user1', 223),
 ('20130102', 'user3', 223),
 ('20130103', 'user2', 323);

createtable colours (name varchar(10), colour_name varchar(10));
insert colours values
 ('user1', 'blue'),
 ('user2', 'green'),
 ('user3', 'brown');

Query:

DECLARE@scols nvarchar(max),
        @colsAS NVARCHAR(MAX),
        @queryAS NVARCHAR(MAX);

select@cols= STUFF((
  SELECT','+ QUOTENAME(C.Name)  
  from (selectdistinct name from [History]) C
  ORDERBY C.Name
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');

select@scols= STUFF((
  SELECT','+ QUOTENAME(Name) +' AS '+ QUOTENAME(colour_Name)  
  from (selectdistinct c.name, x.colour_name
        from [History] C
        JOIN colours x on x.name = c.name) y
  ORDERBY Name
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');

set@query='
    SELECT [Date],'+@scols+' 
    from (
        select [Date], Name, Value
        from [History]
         ) x
    pivot 
         (
        max(value)
        for Name in ('+@cols+')
         ) p ';

-- print @query --<< uncomment this line to see the query that gets generatedexec (@query);

Results:

|DATE|   BLUE |  GREEN |  BROWN |-------------------------------------------------------------| January, 01201300:00:00+0000|123|124|125|| January, 02201300:00:00+0000|223| (null) |223|| January, 03201300:00:00+0000| (null) |323| (null) |

Solution 2:

selectDate, User1 as blue,User2 as green,User3 as brown from tableName

Use query like this.

Make use of 'as' keyword for changing column name.

Solution 3:

To get the mapping You can use a lookup table of old column name to new column name for example

CREATETABLE colname(
  oldname varchar(20),
  newname varchar(20)
)

insertinto colname values ( 'user1','user1 (blue)');
insertinto colname values ( 'user2','user2 (green)');

then you can build an sql statement that uses this mapping

declare@sqvarchar(2000)
set@sq='select date'select@sq=@sq+','+ oldname +' as ['+ newname +']'from colname
set@sq=@sq+'from ( existing query goes here ) 'select@sq

when the sql in @sq looks good you can replace the last select with

exec ( @sq ) 

to run the query