【SQL】动态行转列行转列SQL代码实现

最新推荐文章于 2023-11-17 11:53:58 发布

原创最新推荐文章于 2023-11-17 11:53:58 发布 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

博客介绍了通过输入SQL语句实现动态行转列。数据源有大量行，为显示每个日期的销售数量，不能手动逐列绑定，需动态生成日期列，输入相关代码后完成行转列。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开始的表是这样滴

然后输入SQL语句


select * from (
select ys_Branch.OldBranchDescId, ys_Branch.Name,ys_PayType.PayTypeDesc,ys_Sale.PayPrice 
from ys_SaleDetail 
inner join ys_Sale on ys_SaleDetail.SaleId = ys_Sale.SaleId 
inner join ys_Branch on ys_SaleDetail.BranchId = ys_Branch.BranchId 
inner join ys_SalePayDetail on ys_SaleDetail.SaleId = ys_SalePayDetail.SaleId 
inner join ys_PayType on ys_SalePayDetail.PayType = ys_PayType.PayTypeId 
)as T pivot(sum(T.PayPrice) FOR T.PayTypeDesc in (现金,银联卡,Master卡,Visa卡,代金券,储值卡,支付宝,微信,积分抵扣)) as T

就变成这样啦~~

接下来就是动态行转列的实现

首先一开始数据源是这样子的（qty = 销售数量）

就是有好多好多行啦，我要显示每个日期的销售数量，不可能一列列手动绑定的，所以要动态生成这个日期列

于是输入代码

if object_id('tempdb..#AS') is not null Begin drop table #AS End  /*删除可能会创建过的临时表*/
select * into #AS from 
(select  ys_Branch.Name as 店铺名字,ys_Staff.name as 销售员,sum(ys_SaleDetail.Quantity)as qty,ys_SaleDetail.BusinessDate as 日期 
from ys_SaleDetail   
inner join ys_Sale on ys_SaleDetail.SaleId = ys_Sale.SaleId    
inner join ys_Branch on ys_SaleDetail.BranchId = ys_Branch.BranchId   
inner join ys_Staff on ys_SaleDetail.StaffId = ys_Staff.StaffId  
where ys_SaleDetail.BusinessDate between '2019-2-20' and '2019-3-5'  
group by ys_Branch.Name,ys_Staff.name ,ys_SaleDetail.BusinessDate)  a
DECLARE @sql VARCHAR(8000),@col varchar(1000) 
set @col=(select distinct '['+cast(日期 as nvarchar(1000))+'],'  
from #AS for xml path('')) 
set @col=LEFT(@col,LEN(@col)-1) 
set @sql='SELECT * FROM (select * from #AS)  /*数据源*/ AS P PIVOT  ( SUM(qty/*行转列后 列的值*/) FOR  p.日期/*需要行转列的列*/ IN ('+@col+'/*列的值*/) ) 
AS T' 
EXEC(@sql)

就变成这样子啦