在SQL中动态更改列名

模式设置 ：

create table history (date datetime, name varchar(10), value int);insert history values ('20130101', 'user1', 123), ('20130101', 'user2', 124), ('20130101', 'user3', 125), ('20130102', 'user1', 223), ('20130102', 'user3', 223), ('20130103', 'user2', 323);create table colours (name varchar(10), colour_name varchar(10));insert colours values ('user1', 'blue'), ('user2', 'green'), ('user3', 'brown');

查询 ：

DECLARE @scols nvarchar(max),        @cols AS NVARCHAr(MAX),        @query AS NVARCHAr(MAX);select @cols = STUFF((  SELECT ',' + QUOTENAME(C.Name)    from (select distinct name from [History]) C  ORDER BY C.Name  FOR XML PATH(''), TYPE).value('.', 'NVARCHAr(MAX)'),1,1,'');select @scols = STUFF((  SELECT ',' + QUOTENAME(Name) + ' AS ' + QUOTENAME(colour_Name)    from (select distinct c.name, x.colour_name        from [History] C        JOIN colours x on x.name = c.name) y  ORDER BY 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);|     DATE |   BLUE |  GREEN |  BROWN |-------------------------------------------------------------| January, 01 2013 00:00:00+0000 |    123 |    124 |    125 || January, 02 2013 00:00:00+0000 |    223 | (null) |    223 || January, 03 2013 00:00:00+0000 | (null) |    323 | (null) |