列转行

USE [CoCo_Web]
GO
/****** Object:  StoredProcedure [dbo].[proc_bi_netsales_report]    Script Date: 2020/4/30 14:59:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER  proc [dbo].[proc_bi_netsales_report]
@userId		varchar(10),
@companyname  varchar(50),
@dbareaname	varchar(50),
@gln	varchar(20),
@branchid	varchar(20),
@branchname	varchar(50),
@taxclassname	varchar(20),
@startdate	varchar(25),
@enddate	varchar(25)
as
 

 
--创建行转列临时标题字段表
create table #datetmp(sales_date date null)

--循环存入日期标题数据
declare @up_date date = cast(@startdate as date)
while(@up_date <= cast(@enddate as date))
begin
insert into #datetmp  select @up_date    --使用inser into 表 select 数据语法
select @up_date = DATEADD(DAY,1,@up_date) --插入完毕之后，新增一天
end
--select * from #datetmp
--日期拼接
declare @sql0 varchar(max)  --返回标题中日期部分
SET @sql0 = ( SELECT DISTINCT STUFF(
    ( SELECT  ',' + cast(sales_date as varchar) FROM #datetmp WITH ( NOLOCK )
      order by convert(char(8),sales_date,112)
      FOR XML PATH('')), 1, 1, '') AS T
    FROM   #datetmp)
	--print @sql0

declare @sql1 varchar(max)  --用于列转行
SET @sql1= ( SELECT DISTINCT STUFF(
    ( SELECT  ',[' + cast(sales_date as varchar)+']'FROM #datetmp WITH ( NOLOCK ) order by convert(char(8),sales_date,112) FOR XML PATH('')), 1, 1, ''
	) AS T   FROM   #datetmp)
	--print @sql1
declare @sql2 varchar(max) --用于子查询，如果为空就显示0
SET @sql2= ( SELECT DISTINCT STUFF(
    ( SELECT  '+isnull([' + cast(sales_date as varchar)+'],0)'FROM #datetmp WITH ( NOLOCK ) order by convert(char(8),sales_date,112) FOR XML PATH('')), 1, 1, ''
	) AS T  FROM   #datetmp)
	
	--print @sql2
declare @sql3 varchar(max)  --赋标题日期别名
SET @sql3= ( SELECT DISTINCT STUFF(
    ( SELECT  ',isnull([' + cast(sales_date as varchar)+'],0) as [' + cast(sales_date as varchar)+']'FROM #datetmp WITH ( NOLOCK )
      order by convert(char(8),sales_date,112)   FOR XML PATH('')), 1, 1, '') AS T    FROM   #datetmp)
	--print @sql3
--0返回动态标题
Declare @output varchar(max)
set @output ='分公司,区域,全球编码,CN编码,门店名称,财务分类,当月合计,'+@sql0
select @output as title 


 --生成交叉表，获取财务分类详细数据
declare @sql varchar(max)
SET @sql= 'SELECT regionName 分公司,dbareaname 区域,gln 全球编码,branch_id CN编码,branchname 门店名称,tax_classname 财务分类,total 当月合计,'+@sql3+'  FROM
(SELECT *,'+@sql2+' as total from (
SELECT b.regionname,b.dbareaname,b.gln,b.branch_id,b.branchname,n.tax_classname 
,n.sales_Date,n.net_total,b.opendate
FROM sys_user u 
join sys_user_branchoffice ub on u.user_id = ub.user_id
join sys_branchoffice_cn bo	on 	ub.branchoffice_id = bo.branchoffice_id
join business_product_netsales n on bo.dbname=n.dbname and n.sales_Date  between '''+@startdate+''' and '''+@enddate+'''
join bin_View as b on n.branch_id=b.branch_Id and n.dbname=b.dbname 

and (1=(case when '''+isnull(@userId,'')+'''='''+''' then 1 else 0 end) or u.user_id='''+@userId+''')
and (1=(case when '''+isnull(@companyname,'')+'''='''+''' then 1 else 0 end) or b.regionName='''+@companyname+''')
and (1=(case when '''+isnull(@dbareaname,'')+'''='''+''' then 1 else 0 end) or b.dbareaname='''+@dbareaname+''')
and (1=(case when '''+isnull(@gln,'')+'''='''+''' then 1 else 0 end) or b.gln = '''+@gln+''')
and (1=(case when '''+isnull(@branchid,'')+'''='''+''' then 1 else 0 end) or b.branch_id = '''+@branchid+''')
and (1=(case when '''+isnull(@branchname,'')+'''='''+''' then 1 else 0 end) or b.branchname like ''%'+@branchname+'%'')
and (1=(case when '''+isnull(@taxclassname,'')+'''='''+''' then 1 else 0 end) or n.tax_classname = '''+@taxclassname+''')
) AS a 
 PIVOT(SUM(net_total) FOR sales_Date IN ('+@sql1+'))as hj ) AS t
 order by branch_id
 --order by opendate,gln '

 exec (@sql)
 print @sql

  --生成交叉表，获取财务分类合计数据
declare @sqltotal varchar(max)
SET @sqltotal= 'SELECT regionName 分公司,dbareaname 区域,gln 全球编码,branch_id CN编码,branchname 门店名称,''合计'' 财务分类,total 当月合计,'+@sql3+'  FROM
(SELECT *,'+@sql2+' as total from (
SELECT b.regionname,b.dbareaname,b.gln,b.branch_id,b.branchname,n.sales_Date,n.net_total,b.opendate
FROM sys_user u 
join sys_user_branchoffice ub on u.user_id = ub.user_id
join sys_branchoffice_cn bo	on 	ub.branchoffice_id = bo.branchoffice_id
join business_product_netsales n on bo.dbname=n.dbname and n.sales_Date  between '''+@startdate+''' and '''+@enddate+'''
join bin_View as b on n.branch_id=b.branch_Id and n.dbname=b.dbname 

and (1=(case when '''+isnull(@userId,'')+'''='''+''' then 1 else 0 end) or u.user_id='''+@userId+''')
and (1=(case when '''+isnull(@companyname,'')+'''='''+''' then 1 else 0 end) or b.regionName='''+@companyname+''')
and (1=(case when '''+isnull(@dbareaname,'')+'''='''+''' then 1 else 0 end) or b.dbareaname='''+@dbareaname+''')
and (1=(case when '''+isnull(@gln,'')+'''='''+''' then 1 else 0 end) or b.gln = '''+@gln+''')
and (1=(case when '''+isnull(@branchid,'')+'''='''+''' then 1 else 0 end) or b.branch_id = '''+@branchid+''')
and (1=(case when '''+isnull(@branchname,'')+'''='''+''' then 1 else 0 end) or b.branchname like ''%'+@branchname+'%'')
--and (1=(case when '''+isnull(@taxclassname,'')+'''='''+''' then 1 else 0 end) or n.tax_classname = '''+@taxclassname+''')
) AS a 
 PIVOT(SUM(net_total) FOR sales_Date IN ('+@sql1+'))as hj ) AS t
 order by branch_id
 --order by opendate,gln '

 exec (@sqltotal)
-- print @sqltotal