请帮忙给转换一下下列格式
日期             xm           lx
2007-01       aaa           A  
2007-01       bbb           C
2007-01       ccc           C
2007-02       aaa           C  
2007-02       bbb           C
2007-02       ccc           C
2007-03       aaa           C  
2007-03       bbb           C
2007-03       ccc           C
...........

转换的格式为:

xm       2007-01       2007-02     2007-03......
aaa       A                     C               C
bbb       C                     C               C
ccc       C                     C               C
.........

------解决方案--------------------
declare @sql varchar(8000)
set @sql = 'select xm , '
select @sql = @sql + 'max(case 日期 when ' ' '+日期+ ' ' '
then lx else 0 end) as ' ' '+日期+ ' ' ', '
from (select distinct xm from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name '
exec(@sql)


------解决方案--------------------

create table test(日期 varchar(10),xm varchar(10),lx varchar(10))
insert test select '2007-01 ', 'aaa ', 'A '
union all select '2007-01 ', 'bbb ', 'C '
union all select '2007-01 ', 'ccc ', 'C '
union all select '2007-02 ', 'aaa ', 'C '
union all select '2007-02 ', 'bbb ', 'C '
union all select '2007-02 ', 'ccc ', 'C '
union all select '2007-03 ', 'aaa ', 'C '
union all select '2007-03 ', 'bbb ', 'C '
union all select '2007-03 ', 'ccc ', 'C '

declare @s varchar(8000)
set @s= 'select xm '
select @s=@s+ ',max(case 日期 when ' ' '+日期+ ' ' ' then lx end) as [ '+日期+ '] ' from test group by 日期
SET @s=@s+ ' from test group by xm '
exec(@s)

xm 2007-01 2007-02 2007-03
---------- ---------- ---------- ----------
aaa A C C
bbb C C C
ccc C C C

警告: 聚合或其它 SET 操作消除了空值。
------解决方案--------------------
declare @a varchar(1000)

select @a=coalesce(@a+ ', ', ' ')+ ' min( case when 日期= ' ' '+日期+ ' ' ' then lx end) [ '+日期+ '] ' from [Table] group by 日期
exec( 'select xm, '+@a + ' from [Table] group by xm ')