JBOSS 点滴

丰丰的博客

行列转换 sqlserver

忙忙停停花了两天时间弄出来的,这这里与大家共享,这个存储过程实现后,大伙就不用重复劳动啊!
表1
bill
0001
0002
0003
....
要根据表1得出结算如0001,0002,0003,......

declare @num int,
        @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中?

declare @num int,
               @sqls nvarchar(4000)
set @sqls='select @a=count(*) from t_paystock_h '
exec sp_executesql @sqls,N'@a int output',@num output
select @num



alter procedure usp_Tolineout
    @table varchar(50),
    @wherebill varchar(20),
    @orderbill varchar(20),
    @count_bill varchar(20),
    @bill_value varchar(20),
    @print_bill varchar(1000)
as--Author fly
begin
  -- @table 表名 @wherebill 查询条件字段 @orderbill 排序字段 @count_bill 要统计的单号,也是要产生字符串的字段,@bill_value 单据号,@print_bill 得出的结果写入哪个字段
  declare @cw_count int
  declare @i        int
  declare @sql      nvarchar(4000),
          @sql2     nvarchar(4000),
          @str_bill nvarchar(4000)
         
     set @i=1
     set @cw_count=1
     set @sql = N'select   @count = count(distinct '+@count_bill+')   from  '+@table+' where '+@wherebill+'='+''''+@bill_value+''''+''
     exec sp_executesql @sql,N'@count int output',@cw_count output
  
  while (@i<=@cw_count)
   begin
       set @sql2 = N'select top 1 @strbill=isnull(convert(varchar(100),@strbill),'''')+case isnull(@strbill,'''') when ''''  then '''' else '','' end +convert(varchar(30),'+@count_bill+') from
                   (select top '+convert(varchar(10),@i,126)+' '+@count_bill+','+@wherebill+' from '+@table+' where bill='''+@bill_value+''' order by '+@orderbill+' desc) a
                      where '+@wherebill+'='''+@bill_value+''' order by  '+@orderbill+' asc'
     
       exec sp_executesql @sql2,N'@strbill varchar(500) output',@str_bill output
     
       set @i=@i+1
   end /***/
  print '结果为:'+@str_bill
end


网上转载一例:
create table tb(truename varchar(20),shuxue int,yuwen int,yinyu int)
insert tb select
'张三',   100 ,80, 80  union all select
'李四',   90 ,90 ,60  union all select
'王五',   59, 80 ,44

select truename,fz=shuxue+yuwen+yinyu ,1 as id into #t
from tb

declare @s varchar(4000)
select @s=isnull(@s+',','')+'max(case when truename='''+truename+''' then fz else 0 end) ['+truename+']'
from #t
set @s='select id,'+@s+' from #t group by id'

exec(@s)

id          张三          李四          王五
----------- ----------- ----------- -----------
1           260         240         183

(
1 行受影响)
drop table tb
drop table #t

posted on 2009-04-22 16:31 半导体 阅读(243) 评论(1)  编辑  收藏 所属分类: sqlserver

评论

# re: 行列转换 sqlserver 2012-09-06 18:18 邓鹏

S_LabName S_LabMeno
小麦 星耀
我想改成这样:
S_LabName 小麦
S_LabMeno 星耀  回复  更多评论   


只有注册用户登录后才能发表评论。


网站导航: