create table u01
(医案编号 varchar(5),药物编号 varchar(5))
insert into u01
select '01','01' union all
select '01','02' union all
select '01','03' union all
select '02','07' union all
select '02','08' union all
select '03','06' union all
select '04','01' union all
select '04','02' union all
select '04','03' union all
select '04','04' union all
select '04','05' union all
select '05','09' union all
select '05','08'
declare @tsql varchar(6000),@c1 varchar(1000),@c2 varchar(1000)
select @c1=isnull(@c1+',','')+'isnull(['+rtrim(number)+'],'''') ''药物'+rtrim(number)+''' ',
@c2=isnull(@c2+',','')+'['+rtrim(number)+']'
from master.dbo.spt_values
where type='P' and number>=1 and number<=
(select max(c) from (select count(1) 'c' from u01 group by 医案编号) t)
select @tsql='select 医案编号,'+@c1+'
from (select 医案编号,药物编号,
row_number() over(partition by 医案编号 order by getdate()) ''rn''
from u01) t
pivot(max(药物编号) for rn in ('+@c2+')) p '
exec(@tsql)
/*
医案编号 药物1 药物2 药物3 药物4 药物5
----- ----- ----- ----- ----- -----
01 01 02 03
02 07 08
03 06
04 01 02 03 04 05
05 09 08
(5 row(s) affected)
*/
来源：http://bbs.csdn.net/