求一条sql语句

[harryCom]

已知有表a如下:
id item class value

1 i1 c1 v1
2 i2 c1 v2
3 i1 c2 v3
4 i2 c2 v4
5 i3 c2 v5

希望能通过一条sql语句查询出以下结果:
因为class的记录的值是不确定,所以结果的字段c1,c2..也是不确定
item c1 c2 ...

i1 v1 v3 ...
i2 v2 v4 ...
i3 v5 ...

[hellowork]

if object_id('tbTest') is not null
drop table tbTest
GO
----创建测试数据
create table tbTest(id int,item varchar(10),class varchar(10),value varchar(10))
insert tbTest
select 1, 'i1', 'c1', 'v1' union all
select 2, 'i2', 'c1', 'v2' union all
select 3, 'i1', 'c2', 'v3' union all
select 4, 'i2', 'c2', 'v4' union all
select 5, 'i3', 'c2', 'v5'

----动态交叉汇总
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',[' + class + '] = max(case class when ''' + class + ''' then value end)'
from tbTest group by class
set @sql = 'select item' + @sql + ' from tbTest group by item'
EXEC(@sql)

----清除测试环境
drop table tbTest

/*结果
item c1 c2
------------------------------
i1 v1 v3
i2 v2 v4
i3 v5
*/

[chuifengde]

declare @sql varchar(8000)
select @sql=coalesce(@sql+',','')+' min(case when class='''+class +''' then value end) ['+class+']' from A group by class

exec('select item,'+@sql+' from A group by item')

[harryCom]

非常感谢二位