create   table   tb(id int,name varchar(10),price int) 
insert   into   tb   values(1,'a',100) 
insert   into   tb   values(2,'a',200) 
insert   into   tb   values(3,'a',300) 
go 
select name,   
    max(case   when   px   =   1   then   price   else   ' '   end)   'price1 ', 
    max(case   when   px   =   2   then   price   else   ' '   end)   'price2 ', 
    max(case   when   px   =   3   then   price   else   ' '   end)   'price3 ' 
from 
( 
    select  px=(select count(1)   from   tb   where   name=a.name   and   price <a.price)+1,* from   tb   a 
) t 
group by name 
order by count(id) desc
--删除数据
go
drop table tb 
/*
name       price1      price2      price3 
---------- ----------- ----------- -----------
a          100         200         300

(1 row(s) affected)
*/