表结构如图,四个列分别是日期、路名、目的地、运量。每天,每条路的目的地数量不固定
fEventDay | fRoadName | fDestination | fFreight |
20151128 | 苏嘉杭 | 常州 | 163 |
20151128 | 苏嘉杭 | 无锡 | 441 |
20151128 | 苏嘉杭 | 苏州 | 441 |
20151128 | 苏嘉杭 | 南通 | 441 |
要求输出这样的报表,以日期、道路、运量倒序排列。实际报表里的目的地有很多,这里只截取前面的几列
EventDay | RoadName | Destination1 | Freight1 | Destination10 | Freight10 | Destination2 | Freight2 |
20150304 | 绕城高速 | 南通 | 673 | NULL | NULL | 苏州 | 673 |
20150304 | 苏嘉杭 | 常州 | 533 | NULL | NULL | 南通 | 533 |
20150305 | 绕城高速 | 淮安 | 892 | 盐城 | 244 | 连云港 | 892 |
20150305 | 苏嘉杭 | 常州 | 878 | 连云港 | 255 | 苏州 | 878 |
难点在于,目的地的数量不确定,感觉传统的行转列处理方法难以实现,因为分列的参数不固定,需要根据记录动态生成。
为此,设想先生成分列参数,再拼装SQL语句,最后输出结果
实现的存储过程
ALTER PROCEDURE [dbo].[ProcedureName]
(
@S_Day varchar(10),--开始日期
@E_Day varchar(10), --日期范围
@RoadName varchar(300), --道路(多值查询)
@Destination varchar(300), --目的地(多值查询)
@order varchar(max)--排序字段(多值)
)
AS
BEGIN
select fEventDay as EventDay ,[fRoadName] as RoadName,[fDestination] as Destination
,'Destination'+convert(varchar(2), row_number() over(partition by [fEventDay],[fRoadName] order by [fFreight] desc,[fDestination])) as TopNum
into #GoodsDestination
FROM [TOCC].[dbo].[tGoodsDestination] WITH(INDEX(Index_Day)) where 1=1-- and fEventDay=20170320
group by [fEventDay],[fRoadName],[fDestination],[fFreight]
order by [fEventDay],[fRoadName],[fFreight] desc,[fDestination]
DECLARE @topnum VARCHAR(8000)
SET @topnum='' --初始化变量 @sql
select @topnum= @topnum+',[' + convert (varchar(20),TopNum)+']' FROM #GoodsDestination GROUP BY TopNum order by TopNum --变量多值赋值
SET @topnum= STUFF(@topnum,1,1,'')--去掉首个','
select fEventDay as EventDayF ,[fRoadName] as RoadNameF,[fFreight] as Freight
,'Freight'+convert(varchar(2), row_number() over(partition by [fEventDay],[fRoadName] order by [fFreight] desc,[fDestination])) as TopNumF
into #GoodsDestinationF
FROM [TOCC].[dbo].[tGoodsDestination] WITH(INDEX(Index_Day)) where 1=1-- and fEventDay=20170320
group by [fEventDay],[fRoadName],[fDestination],[fFreight]
order by [fEventDay],[fRoadName],[fFreight] desc,[fDestination]
DECLARE @topnumf VARCHAR(8000)
SET @topnumf='' --初始化变量 @sql
select @topnumf= @topnumf+',[' + convert (varchar(20),TopNumF)+']' FROM #GoodsDestinationF GROUP BY TopNumF order by TopNumF --变量多值赋值
SET @topnumf= STUFF(@topnumf,1,1,'')--去掉首个','
DECLARE @strSql VARCHAR(8000)
SET @strSql='select * from (' --初始化变量 @sql
SET @strSql= @strSql+ '(select EventDay, RoadName'
--
set @strSql = @strSql+ ','+@topnum
set @strSql = @strSql+'from #GoodsDestination pivot (max(Destination) for TopNum in ('+@topnum+'))a where 1=1'
if @S_Day is not null and @S_Day<>''
set @strSql = @strSql+' and EventDay '+@S_Day+'';
if @E_Day is not null and @E_Day<>''
set @strSql = @strSql+' and EventDay '+@E_Day+'';
if @RoadName is not null and @RoadName<>''
set @strSql = @strSql+' and RoadName in ('+@RoadName+')';
if @Destination is not null and @Destination<>''
set @strSql = @strSql+' and Destination in ('+ @Destination+')';
set @strSql = @strSql+') d';
set @strSql = @strSql+' left join ';
SET @strSql= @strSql+ '(select EventDayF, RoadNameF'
--
set @strSql = @strSql+ ','+@topnumf
set @strSql = @strSql+'from #GoodsDestinationF pivot (max(Freight) for TopNumF in ('+@topnumf+')) a where 1=1'
if @S_Day is not null and @S_Day<>''
set @strSql = @strSql+' and EventDay '+@S_Day+'';
if @E_Day is not null and @E_Day<>''
set @strSql = @strSql+' and EventDay '+@E_Day+'';
if @RoadName is not null and @RoadName<>''
set @strSql = @strSql+' and RoadName in ('+@RoadName+')';
if @Destination is not null and @Destination<>''
set @strSql = @strSql+' and Destination in ('+ @Destination+')';
set @strSql = @strSql+') f';
set @strSql = @strSql+' on d.EventDay=f.EventDayF and d.RoadName=f.RoadNameF ';
set @strSql = @strSql+') ';
if @order is not null and @order<>''
set @strSql = @strSql+@order
else
set @strSql = @strSql+' order by Eventday, RoadName'
PRINT @strSql
exec(@strSql)
END
EventDay | RoadName | Destination1 | Destination10 | Destination2 | Destination3 | Destination4 | Destination5 | Destination6 | Destination7 | Destination8 | Destination9 | Freight1 | Freight10 | Freight2 | Freight3 | Freight4 | Freight5 | Freight6 | Freight7 | Freight8 | Freight9 |
20150304 | 绕城高速 | 南通 | NULL | 苏州 | 盐城 | 连云港 | 徐州 | 常州 | 南京 | 无锡 | 镇江 | 673 | NULL | 673 | 673 | 547 | 547 | 396 | 396 | 396 | 396 |
20150304 | 苏嘉杭 | 常州 | NULL | 南通 | 苏州 | 无锡 | 连云港 | 徐州 | 盐城 | 南京 | 镇江 | 533 | NULL | 533 | 533 | 533 | 406 | 406 | 406 | 255 | 255 |
20150305 | 绕城高速 | 淮安 | 盐城 | 连云港 | 徐州 | 常州 | 南京 | 镇江 | 南通 | 苏州 | 无锡 | 892 | 244 | 892 | 892 | 741 | 741 | 741 | 244 | 244 | 244 |
20150305 | 苏嘉杭 | 常州 | 连云港 | 苏州 | 无锡 | 镇江 | 南京 | 淮安 | 南通 | 徐州 | 盐城 | 878 | 255 | 878 | 878 | 878 | 601 | 382 | 382 | 382 | 382 |
但是有BUG,由于目的地和运量是分别在两个查询输出,同时,列的数量不确定,合并时没法按要求输出
由此还带来另一个问题--排序时,以字符串形式的Destination10会排在Destination2的前面,实际应该在最后一列。
不知道有什么办法可以解决,欢迎大家指教,谢谢!