select 日期,case 价格名称 when 价格1  then 结算价 else 0 end  as 价格1结算价,
           case 价格名称 when 价格1  then 销售价 else 0 end  as 价格1销售价,
           case 价格名称 when 价格2  then 结算价 else 0 end  as 价格2结算价,
           case 价格名称 when 价格2  then 销售价 else 0 end  as 价格2销售价
from TB
group by  日期

------解决方案--------------------
大致如下:
SQL code

select 日期,
case when 价格名称 = 价格1 then 结算价 else null end as 价格1结算价,
case when 价格名称 = 价格1 then 销售价 else null end as 价格1销售价,
case when 价格名称 = 价格2 then 结算价 else null end as 价格2结算价,
case when 价格名称 = 价格2 then 销售价 else null end as 价格2销售价
from tb
group by  日期

------解决方案--------------------
SQL code
declare @sql varchar(8000)
set @sql = 'select 日期 '
select
 @sql = @sql + ' , 
 max(case 价格名称 when ''' + 价格名称 + ''' then 结算价 else 0 end) [' + 价格名称 + '],
 max(case 价格名称 when ''' + 价格名称 + ''' then 销售价 else 0 end) [' + 价格名称 + ']'
from
 (select distinct 价格名称 from tb) as a
set @sql = @sql + ' from tb group by 日期'
exec(@sql)

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

SQL code
----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-06-21 10:20:08
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([日期] datetime,[价格名称] varchar(5),[结算价] int,[销售价] int)
insert [tb]
select '2012-1-1','价格1',1000,1200 union all
select '2012-1-1','价格2',1000,1200 union all
select '2012-1-2','价格1',1000,1200 union all
select '2012-1-2','价格2',1000,1200
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select 日期 '
select @sql = @sql + ' , 
 max(case 价格名称 when ''' + 价格名称 + ''' then 结算价 else 0 end) [' + 价格名称 + '],
 max(case 价格名称 when ''' + 价格名称 + ''' then 销售价 else 0 end) [' + 价格名称 + ']'
from
 (select distinct 价格名称 from tb) as a
set @sql = @sql + ' from tb group by 日期'
exec(@sql) 
----------------结果----------------------------
/* 日期                      价格1         价格1         价格2         价格2
----------------------- ----------- ----------- ----------- -----------
2012-01-01 00:00:00.000 1000        1200        1000        1200
2012-01-02 00:00:00.000 1000        1200        1000        1200

(2 行受影响)

*/

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

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[日期] datetime,
[价格名称] varchar(5),
[结算价] int,
[销售价] int
)
go
insert [test]
select '2012-1-1','价格1',1000,1200 union all
select '2012-1-1','价格2',1000,1200 union all
select '2012-1-2','价格1',1000,1200 union all
select '2012-1-2','价格2',1000,1200
go
if OBJECT_ID('pro_test')is not null
drop proc pro_test
go
create proc pro_test
(
@PriceName varchar(20)
)
as
declare @str varchar(2000)
set @str=''
select
    @str=@str+',['+价格名称+'结算价]=max(case when [价格名称]='
    +QUOTENAME([价格名称],'''')+' then [结算价] else 0 end),['
    +价格名称+'销售价]=max(case when [价格名称]='+QUOTENAME([价格名称],'''')
    +' then [销售价] else 0 end)'
from 
    test
where
    CHARINDEX([价格名称],@PriceName)>0
group by 
    [价格名称]
print @str
set @str='select convert(varchar(10),[日期],120) as [日期]'
    +@str+' from test where charindex([价格名称],'+quotename(@PriceName,''