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.在一个杂乱无章的表里想要查找特定行的记录,请多多指教!多谢!
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.在一个杂乱无章的表里想要查找特定行的记录,请多多指教!多谢!
pts_sm1这些列名是根据月份生成的是吗?
pts_sm2=sum(case 月份 when 2 then pts_sl else null end),
pts_sm3=sum(case 月份 when 3 then pts_sl else null end),
pts_sm4=sum(case 月份 when 4 then pts_sl else null end),
pts_sm5=sum(case 月份 when 5 then pts_sl else null end)
from <table>
group by pts_id
order by pts_id
create table yy(pts_id varchar(10),月份 varchar(10),pts_sl int)
insert into yy 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',10000declare @sql nvarchar(4000)
set @sql='select pts_id'
select @sql=@sql
+','+quotename(N'pts_sm'+月份)
+N'=sum(case 月份 when '+quotename(月份,N'''')
+N' then pts_sl end)'
from yy group by 月份exec(@sql+N' from yy group by pts_id')drop table yy
insert into yy 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',10000declare @sql nvarchar(4000)
set @sql='select pts_id'
select @sql=@sql
+','+quotename(N'pts_sm'+月份)
+N'=sum(case 月份 when '+quotename(月份,N'''')
+N' then pts_sl end)'
from yy group by 月份print @sql
exec(@sql+N' from yy group by pts_id')drop table yy
--不知道是不是楼主的数据写错了,0001编号的3月份的为400
insert into yy select '0001','1',1000
--union all select '0001','1',400
union all select '0001','3',400 --改成3月份就是楼主要的结果
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',10000declare @sql nvarchar(4000)
set @sql='select pts_id'
select @sql=@sql
+','+quotename(N'pts_sm'+月份)
+N'=sum(case 月份 when '+quotename(月份,N'''')
+N' then pts_sl end)'
from yy group by 月份print @sql
exec(@sql+N' from yy group by pts_id')drop table yy