SQL一行多列的行转列处理
表结构如图,四个列分别是日期、路名、目的地、运量。每天，每条路的目的地数量不固定
|
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的前面，实际应该在最后一列。
不知道有什么办法可以解决，欢迎大家指教，谢谢！
