请帮忙给转换一下下列格式
日期 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 ')