模式设置 ：
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) |
