所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

--		drop table ##temp  create table ##temp ( 住院号 varchar(20), 床位 varchar(20), 应收金额 numeric(20,3), 优惠金额1 numeric(20,3), 优惠金额2 numeric(20,3), 优惠金额3 numeric(20,3), 优惠金额4 numeric(20,3) )  insert into ##temp select '00000','111',1000, 0,0,0,10 union all select '00001','112',1000, 0 ,0,0,0 union all select '00002','113',1000, 0,0,0,0 union all select '00003','114',1000, 0 ,0,0,20 union all select '00004','115',1000, 0,2,0,3 union all select '00005','116',1000, 0,0,0,0 union all select '00006','117',1000, 0,0,0,0   go 	 declare @sql nvarchar(max); declare @sql_delete_column nvarchar(max);  declare @tb table(column_name nvarchar(100),rownum int) declare @count int; declare @i int; declare @return int;  declare @temp_name nvarchar(100); declare @del_column nvarchar(100);  set @sql = ''; set @sql_delete_column  = '';  --临时表名 set @temp_name = '##temp'  --需要删除的列名 set @del_column = '%优惠金额%';   insert into @tb select --t.name,        c.name as column_name,        row_number() over(order by @@servername) as rownum        --c.column_id from tempdb.sys.tables t inner join tempdb.sys.columns c         on t.object_id = c.object_id where t.name = @temp_name                and c.name like @del_column;   set @count = (select count(*) from @tb); set @i = 1;  while @i <= @count begin 	set @sql = 'select @return=sum('+  	           (select column_name from @tb where rownum = @i) + 	           ') from ' + @temp_name; 	     	exec sp_executesql @sql,N'@return int output',@return output; 	 	select @sql_delete_column =  	          @sql_delete_column + 	          case when @return <> 0 then ' ' 	                    else 'alter table '+@temp_name + 	                         ' drop column '+ 	                         (select column_name from @tb where rownum = @i) + 	                         ';' 	               end     set @i = @i +1  end   --动态生成的删除列语句 select @sql_delete_column /* (无列名) alter table ##temp drop column 优惠金额1;  alter table ##temp drop column 优惠金额3;  */   --删除列 exec(@sql_delete_column)   --查询数据 select * from ##temp; /* 住院号	床位	应收金额	优惠金额2	优惠金额4 00000	111	1000.000	0.000	10.000 00001	112	1000.000	0.000	0.000 00002	113	1000.000	0.000	0.000 00003	114	1000.000	0.000	20.000 00004	115	1000.000	2.000	3.000 00005	116	1000.000	0.000	0.000 00006	117	1000.000	0.000	0.000 */

drop table tb go  create table tb( 型号 varchar(20),年 int, 月 int, 日 int,  准确率 int, 缺到率 int,可用率 int )  insert into tb select 'thd' ,2013, 1, 1 , 56 ,  23  ,  34 union all select 'thd', 2013 ,1 ,1  ,66   ,77    ,54 union all select 'thd', 2013 ,1 ,1  ,78   ,55    ,77 union all select 'hhh', 2012 ,9 ,18 ,89   ,55    ,23 union all select 'hhh', 2012 ,9 ,18 ,33   ,37    ,45 union all select 'hhh', 2012 ,9 ,18 ,67   ,56    ,12 go   declare @sql nvarchar(max);  set @sql = '';  ;with t as ( select *,        ROW_NUMBER() over(partition by 型号,年,月,日 order by @@servername) as rownum from tb )  select         @sql = @sql + ',max(case when rownum = '+cast(rownum as varchar)+' then 准确率 else null end) as 准确率' +                      ',max(case when rownum = '+cast(rownum as varchar)+' then 缺到率 else null end) as 缺到率' +                      ',max(case when rownum = '+cast(rownum as varchar)+' then 可用率 else null end) as 可用率' from t group by rownum   select @sql = 'select 型号,年,月,日' + @sql +                ' from (select *,                             ROW_NUMBER() over(partition by 型号,年,月,日 order by @@servername) as rownum                      from tb)t' +               ' group by 型号,年,月,日'                --select @sql  exec(@sql) /* 型号	年	月	日	准确率	缺到率	可用率	准确率	缺到率	可用率	准确率	缺到率	可用率 hhh	2012	9	18	89	55	23	33	37	45	67	56	12 thd	2013	1	1	56	23	34	66	77	54	78	55	77 */

sql server 2000，考勤表：

if OBJECT_ID('tempdb..#temp') is not null    drop table #temp     if OBJECT_ID('tempdb..#temp1') is not null    drop table #temp1        select *,identity(int,1,1) as id into #temp from 考勤表  select *,        (select COUNT(*) from #temp b          where a.empno = b.empno and a.carddate = b.carddate and a.id >= b.id) rownum        into #temp1 from #temp a   declare @sql nvarchar(max); set @sql = '';  select         @sql = @sql + ',max(case when rownum = '+cast(rownum as varchar)+' then cardtime2 else null end) as cardtime'+ cast(rownum as varchar) from #temp1 group by rownum   select @sql = 'select carddate,empno' + @sql +                ' from #temp1' +               ' group by carddate,empno                 order by empno,carddate desc'                --select @sql  exec(@sql) /* carddate	empno	cardtime1	cardtime2	cardtime3	cardtime4 2014-02-12 00:00:00.000	A012152	13:23:16	12:04:01	17:35:21	07:45:56 2014-02-11 00:00:00.000	A012152	13:19:18	12:03:26	07:44:19	17:35:22 2014-02-10 00:00:00.000	A012152	17:34:14	13:22:35	12:02:54	07:44:33 */