使用动态SQL实现行列转换和按列递减的功能

来源：互联网发布：淘宝上的情趣内衣模特编辑：程序博客网时间：2024/06/04 20:02

      /*
基本需求：
库房里会记录每个物料每周的使用数量。
报表要求显示物料的初始数量，及每周使用后的剩余数量。

本例子使用两个功能，
第一：行列转换
第二：在行列转换的基础上，实现按列递减的功能

为了方便阅读，对实现过程进行简化，对表进行简化，不加入过多的其他条件
这是极简化版本
直接复制到 2005及以上的查询分析器，即可运行得出结果
*/

declare @d_startdate datetime -- 开始的时间
declare @i_week int -- 计算的周数
declare @i int -- 循环变量

declare @c_month varchar(10)

declare @c_sql varchar(max)
declare @c_sqlcalu varchar(max)

declare @month_name varchar(10)

set @d_startdate = '2014-01-01'
set @i_week = 10

-- 为了示例方便，把数据放在表变量里，并且都放在动态语句里
set @c_sql = '
-- 表变量，存储示例数据，记录每周使用数量
declare @tab_used table (
version_id bigint not null, -- 物料ID
plan_week varchar(10) not null, -- 使用的周
amount int not null, -- 使用的数量
primary key (version_id,plan_week) )

-- 表变量，存储示例数据，初始数量
declare @tab_init table (
version_id bigint not null, -- 物料ID
init_amount int not null, -- 初始库存数量
primary key (version_id) )

-- 写入初始数据
insert into @tab_used ( version_id,plan_week,amount )
select 1,''2014_WK01'',100
union select 1,''2014_WK02'',100
union select 1,''2014_WK03'',100
union select 1,''2014_WK04'',100
union select 1,''2014_WK05'',100
union select 1,''2014_WK06'',100
union select 1,''2014_WK07'',100
union select 1,''2014_WK08'',100
union select 1,''2014_WK09'',100
union select 1,''2014_WK10'',100
union select 2,''2014_WK02'',20
union select 2,''2014_WK03'',20
union select 2,''2014_WK04'',20
union select 2,''2014_WK05'',20
union select 2,''2014_WK06'',20
union select 2,''2014_WK07'',20
union select 2,''2014_WK08'',20
union select 2,''2014_WK09'',20
union select 2,''2014_WK10'',20
union select 3,''2014_WK06'',50
union select 3,''2014_WK07'',50
union select 3,''2014_WK08'',50
union select 3,''2014_WK09'',50
union select 3,''2014_WK10'',50

insert into @tab_init ( version_id,init_amount )
select 1,10000
union select 2,20000
union select 3,30000
union select 4,40000

-- 动态语句开始
select max(e.init_amount) init_amount,'

set @c_sqlcalu = ' '

set @i = 0

while @i < @i_week
begin
set @c_month = convert(varchar(4),dateadd(ww,@i,@d_startdate),120) + '_WK' + right('0' + cast(datepart(ww,dateadd(ww,@i,@d_startdate)) as varchar(10)),2)
set @month_name = @c_month

-- 根据动态语句实现递减
set @c_sqlcalu = @c_sqlcalu + ' - max(case when plan_week = ''' + @c_month + ''' then amount else 0 end ) '
set @c_sql = @c_sql + ' isnull(max(e.init_amount),0) ' + @c_sqlcalu + ' [r-' + @month_name + '] ,'
set @i = @i + 1
end

set @c_sql = @c_sql + ' mv.version_id
from @tab_used mv
left join @tab_init e on mv.version_id = e.version_id
group by mv.version_id '

exec ( @c_sql )

0 0