动态转换表的结构

[mjtalhx]

1.动态转换表的结构.原表的结构是:
pts_id 月份 pts_sl
0001 1 1000
0001 1 400
0002 2 2000
0003 2 4000
0004 3 3000
0002 4 50000
0003 3 30000
0004 5 10000
转换后的表结构是:
pts_id pts_sm1 pts_sm2 pts_sm3 pts_sm4 pts_sm5
0001 1000 400
0002 2000 50000
0003 30000 4000
0004 10000 3000


2.在一个杂乱无章的表里想要查找特定行的记录,请多多指教!多谢!

[ping3000]

create table tb(pts_id varchar(20),月份 int,pts_sl int)
insert into tb(pts_id,月份,pts_sl)
select '0001',1,1000 union all
select '0001',1,400 union all
select '0002',2,2000 union all
select '0003',2,4000 union all
select '0004',3,3000 union all
select '0002',4,50000 union all
select '0003',3,30000 union all
select '0004',5,10000
go

declare @sql nvarchar(2000)
set @sql = 'select pts_id'
select @sql = @sql + ',isnull(sum(case 月份 when ''' + ltrim(str(月份)) + '''then pts_sl end),0) as pts_sm' + ltrim(str(月份))
from (select distinct 月份 from tb) as t
set @sql = @sql + ' from tb group by pts_id'
exec(@sql)

drop table tb