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的前面，实际应该在最后一列。

不知道有什么办法可以解决，欢迎大家指教，谢谢！

 

转载于:https://www.cnblogs.com/onlyone0218/p/8680683.html