sql 多条记录如何合并成一条记录

我的原表记录如下：
spbm      afield      jg      shrq
0100135 零售价格 7.0000 2007-04-16 15:05:56.250
0100135 会员价         7.0000 2007-04-16 15:05:56.250
0100135 末批进价 5.5000 2007-04-16 15:05:56.250
0100135 零售价格 7.5000 2007-12-19 09:07:55.903
0100135 末批进价 6.5000 2007-12-19 09:07:55.903
还有很多spbm不一样的记录，但每个spbm都按上面那样处理

在建立新表，存放一条记录时，不同的afield，要增加相应的字段
最终形成的记录:spbm,(不同的afield),（相同afield，shrq最大的,yjg的值填到新增的相应字段）,相同afield最大的shrq

如上表，形成的记录应该为
spbm    零售价格    会员价    末批进价    shrq
0100135  7.5        7      6.5      2007-12-19 09:07:55.903

这个比较复杂，这样的sql语句应该怎么写呀，我折腾不出来，请各位高手帮我想想办法？

//------------------------------------------------------------------------------
例子:

我现在有两个表第一个表
ID NAME   BOOK    DATA
1  JOKE   SHUXUE  2004-12-12

...第二个表S_ID ID S_LIKEMAN picre 0001   1    小张         12
0002   1    小李         13
我现在要怎么写SQL语句.才出现
BOOK   S_LIKEMAN   picre S_LIKEMAN   picre
shuxue 小张          12     小李         13
意思就是书只能显示一次,他们的联系人都要显示.

create table tb1(id int,name varchar(20),book varchar(20),date datetime)
insert tb1 values(1,'JOKE','SHUXUE','2004-12-12')

create table tb2(s_id varchar(10),id int,s_likeman varchar(20),pricre int)
insert tb2 values('0001',1,'小张',12)
insert tb2 values('0002',1,'小李',13)

declare @sql varchar(8000)
set @sql='select a.book'
select @sql=@sql+',max(case when b.s_likeman='''+s_likeman+''' then b.s_likeman end) s_likeman,
          max(case when b.pricre='+cast(pricre as varchar)+' then b.pricre end) pricre'
from (select distinct s_likeman,pricre from tb2) a

exec (@sql+' from tb1 a,tb2 b where a.id=b.id group by a.book')

drop table tb1,tb2

/*
book                 s_likeman            pricre      s_likeman            pricre     
-------------------- -------------------- ----------- -------------------- -----------
SHUXUE               小李                   13          小张                   12
*/

 

//---------------------------------------------------------------------------------------------

create table a(spbm  varchar(10),afield   varchar(10),jg decimal(20,4),shrq  datetime)
insert a select '0100135','零售价格', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'会员价',  7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union all select '0100135', '零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union all select '0100135', '末批进价', 6.5000 ,'2007-12-19 09:07:55.903'

declare @sql varchar(8000)
set @sql='select spbm,'
select @sql=@sql+'max(case when afield='''+afield+''' then jg end) ['+afield+'],' from a group by afield

set @sql=@sql+'max(shrq) shrq from a group by spbm'
exec(@sql)

--result
/*spbm       会员价                    零售价格                   末批进价                   shrq                                                  
---------- ---------------------- ---------------------- ---------------------- ------------------------------------------------------
0100135    7.0000                 7.5000                 6.5000                 2007-12-19 09:07:55.903
*/

 

//---------------------------------------------------------------------------------------

create table tb(spbm  varchar(10),afield varchar(10),jg decimal(20,4),shrq  datetime)
insert tb select '0100135','零售价格', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'会员价',  7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union all select '0100135', '零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union all select '0100135', '末批进价', 6.5000 ,'2007-12-19 09:07:55.903'
go
select spbm ,
  max(case afield when '零售价格' then jg else 0 end) '零售价格',
  max(case afield when '会员价' then jg else 0 end) '会员价',
  max(case afield when '末批进价' then jg else 0 end) '末批进价',
  max(shrq) shrq
from tb
group by spbm

drop table tb

/*
spbm       零售价格                                    会员价                                     末批进价                                    shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135    7.5000                                  7.0000                                  6.5000                                  2007-12-19 09:07:55.903

(1 行受影响)
*/


//-----------------------------------------------------------------------------------------

create table tb(spbm  varchar(10),afield varchar(10),jg decimal(20,4),shrq  datetime)
insert tb select '0100135','零售价格', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'会员价',  7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union all select '0100135', '零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union all select '0100135', '末批进价', 6.5000 ,'2007-12-19 09:07:55.903'
go

--静态SQL,指afield值固定为这三种。
select spbm ,
  max(case afield when '零售价格' then jg else 0 end) '零售价格',
  max(case afield when '会员价' then jg else 0 end) '会员价',
  max(case afield when '末批进价' then jg else 0 end) '末批进价',
  max(shrq) shrq
from tb
group by spbm
/*
spbm       零售价格                                    会员价                                     末批进价                                    shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135    7.5000                                  7.0000                                  6.5000                                  2007-12-19 09:07:55.903

(1 行受影响)
*/

--动态SQL，指afield值不固定。
declare @sql varchar(8000)
set @sql = 'select spbm'
select @sql = @sql + ' , max(case afield when ''' + afield + ''' then jg else 0 end) [' + afield + ']'
from (select distinct afield from tb) as a
set @sql = @sql + ',max(shrq) shrq from tb group by spbm'
exec(@sql)
/*
spbm       会员价                                     零售价格                                    末批进价                                    shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135    7.0000                                  7.5000                                  6.5000                                  2007-12-19 09:07:55.903

(1 行受影响)
*/

drop table tb
//---------------------------------------------------------------------

select spbm, max(零售价格), max(会员价), max(末批进价), max(shrq)
from
(
select spbm,
case when afield = '零售价格' then max(jg) as 零售价格,
case when afield = '会员价' then max(jg) as 会员价,
case when afield = '末批进价' then max(jg) as 末批进价,
max(shrq)
from

table_1 group by spbm, afield
) a
group by spbm

//----------------------------------------------------------

declare @sql varchar(6000)
  set @sql='select spbm'
  select @sql=@sql+',max(case afield when '''+afield+''' then jg else 0 end)['+afield+']'
  from (select distinct afield from ta) as t
  select @sql=@sql+',max(shrq) from ta group by spbm'
exec(@sql)