将日期行转换为列
以下数据
a 2007-1-1
b 2007-1-3
a 2007-2-3
a 2007-4-5
a 2007-6-8
b 2007-3-3
c 2007-3-3
d 2007-3-9
得到
字段1 字段2 字段3 字段4 字段5
a 2007-1-1 2007-2-3 2007-4-5 2007-6-8
b 2007-1-3 2007-3-3
c 2007-3-3
d 2007-3-9
谢谢
[解决办法]
create table ta(name varchar(2), date datetime)
insert ta select 'a ', '2007-1-1 '
insert ta select 'b ', '2007-1-3 '
insert ta select 'a ', '2007-2-3 '
insert ta select 'a ', '2007-4-5 '
insert ta select 'a ', '2007-6-8 '
insert ta select 'b ', '2007-3-3 '
insert ta select 'c ', '2007-3-3 '
insert ta select 'd ', '2007-3-9 '
--通过临时表
select *,id=1 into #
from ta order by name asc,date asc
--更新临时表id为记录数
declare @a varchar(2),@i int
update #
set @i=case when name=@a then @i+1 else 1 end,
@a=name,id=@i
--查询
declare @sql varchar(4000)
set @sql= 'select name '
select @sql=@sql+ ',[记录 '+rtrim(id)+ ']=max(case when id= '+rtrim(id)+
' then convert(varchar(10),date,120) else ' ' ' ' end) '--不显示null时
from # group by id
exec(@sql+ ' from # group by name ')
name 记录1 记录2 记录3 记录4
---- ---------- ---------- ---------- ----------
a 2007-01-01 2007-02-03 2007-04-05 2007-06-08
b 2007-01-03 2007-03-03
c 2007-03-03
d 2007-03-09
(4 行受影响)
