当前位置: 代码迷 >> SQL >> sqlserver表格统计——参数化动态PIVOT行转列
  详细解决方案

sqlserver表格统计——参数化动态PIVOT行转列

热度:9   发布时间:2016-05-05 11:01:47.0
sqlserver报表统计——参数化动态PIVOT行转列


先列出所有表结构:

--title 报表--table 实收水费:hx_t_received --字段 收费部门id:hx_fdepartmentid  1、收费部门名称:hx_fdepartmentname 应收水费信息ID:hx_freceivableid--收费类别:hx_ftype (水费(1):再分[开户(table) 供水类别:自来水(100000001)==水费,中水(100000002)==中水费],代理费(2)==污水处理费)--实收金额:hx_freceivedamount  创建时间:createdon 支付方式:hx_fpayway(现金:100,000,000与支票:100,000,001)--交易状态:hx_fstate(入账100,000,000: 支票付款100,000,001 除:作废:100,000,002、银行付款:100,000,003)--票据信息:hx_finvoiceid 收费员:CreatedByName 创建时间:createdon  主键:hx_t_receivedid--收费部门:hx_fdepartmentid  水表ID:hx_fmeterid 修改时:modifiedon  催费员id:hx_fdunid 收费员:createdby--table 应收水费:hx_t_receivable--字段  水量信息ID:hx_fusedamountid 主键:hx_t_ReceivableId 客户信息:hx_faccountid  水表:hx_fmeterid--记录状态:hx_fstate(支票付款中:100,000,002)--table 用水水量:hx_t_waterusedamount--字段  开户信息:hx_fcustomerandmeterrelaid 主键:hx_t_WaterUsedAmountId  水表:hx_fmeterid 区段:hx_fzone 实用水量:hx_famount --估水原因:hx_festimateamountreason  结算水量:hx_fpayamount--抄表类型hx_frecordtype (估水:100,000,001 未抄见:100,000,002 正常:100,000,000 预付费卡表估水:100,000,003) --水量状态:hx_fstate(计划:100,000,000,已录入水量:100,000,001,已生成应收:100,000,006 本次抄表止度:hx_freading--用水年份:hx_fyear  用水月份:hx_fmonth--table  客户:account--字段  主键:accountid 客户名称:name 客户编码:accountnumber--table 客户账户交易明细:hx_t_transactiondetails--字段 交易类型:hx_ftranstype(账户充值:100,000,000) 交易金额:hx_ftransamount  实收水费信息:hx_freceivedid 收费员:createdby 区段:hx_fzoneid--交易状态:hx_fstate(作废:100,000,002) 票据信息:hx_finvoiceid 支付方式:hx_fpayway--table  客户充值账户:hx_t_rechargeaccount--主键;hx_t_rechargeaccountid 水表ID:hx_fmeterid 客户ID:hx_fbuildinaccountid--table 开户信息:hx_t_customerandmeterrela--区段:hx_fzoneid 主键:hx_t_customerandmeterrelaid 水表:hx_fmeterid 客户:hx_faccountid--********二********-- table 临时用水实收:hx_t_tempwaterreceived  主键:hx_t_tempwaterreceivedid--字段 临时用水:hx_ftempwaterid   支付方式:hx_fpayway(现金:100,000,000与支票:100,000,001)--交易状态:hx_fstate(除:作废:100,000,002、银行付款:100,000,002)--票据信息:hx_finvoiceid  收费部门ID:hx_fdepartmentid--table 临时用水:hx_t_tempwater--字段  主键:hx_t_tempwaterid  负责人:ownerid  收费项目:hx_ffeeitems(水费(再分)、污水费) 供水类别:hx_fwatertype  区段:hx_fzoneid--水量:hx_fusedamount--table  用户:systemuser--字段  主键:SystemUserId 业务部门:businessunitid 全名:FullName--table 部门:businessunit--字段 主键:businessunitid  名称:name--table 区段信息:hx_t_zone--字段  主键:hx_t_zoneid 团队信息:hx_fteamid  抄表员:hx_frecorder 区段号:hx_fzone--部门:hx_fbureau  催费员:hx_fdunname(查找)  部门名称:hx_fbureauName --table 征费所 hx_t_levyinstitute--字段 主键:hx_t_levyinstituteid 名称:hx_fname--table 抄表班组归属hx_t_teamattribution--字段 主键:hx_t_teamattributionid 抄表班组信息:hx_fteamid 征费所信息:hx_flevyinstituteid--table 职员:hx_t_staff--字段 姓名:hx_name 主键:hx_t_staffid
SQL代码:

--title查表员估收报表declare @begin_date datetimedeclare @end_date datetimeselect @begin_date = getdate()declare @hx_ClosingAccountInfoId uniqueidentifierset @hx_ClosingAccountInfoId='08AFEF9F-E174-46F2-855A-32F26BE38F3B'--2014年09月01期declare @BusinessunitId uniqueidentifier --征费所set @BusinessunitId='6A2537DD-B87A-E411-93FE-002590622897' ----第十五收费所DECLARE @TeamId uniqueidentifier --抄表班组SET @TeamId=null--'18ACBD81-5B45-E411-9402-6CAE8B22702D' --第十五收费所(零散查表组)-----------------SQL正文----------------------------------------------------------执行时间:declare @FMonth intdeclare @FYear intdeclare @FTimes intselect @FYear=t.hx_FYear,@FMonth=t.hx_FMonth,@FTimes=t.hx_FTimes from hx_ClosingAccountInfo t where [email protected]_ClosingAccountInfoId--select @FYear,@FMonth,@FTimesbeginif object_id('tempdb..#t_estimateamountreason') is not null drop table #t_estimateamountreasonif object_id('tempdb..#t_EstimateamountReasonTotal') is not null drop table #t_EstimateamountReasonTotalselect * into #t_estimateamountreason from (select distinct t1.Label,t2.Value from MetadataSchema.LocalizedLabel t1 inner join MetadataSchema.AttributePicklistValue t2 on t1.ObjectId=t2.AttributePicklistValueId inner join MetadataSchema.OptionSet t3 on t2.OptionSetId=t3.OptionSetId  where t3.Name='hx_estimateamountreason_values' and t1.ObjectColumnName='DisplayName' and t1.LanguageId=2052) tselect * into #t_EstimateamountReasonTotal from(select w.hx_frecordername fullname,--抄表员w.hx_fzone hx_fzone, --区段号1 mcounts,e.Label Label,w.hx_fpayamount hx_fpayamount, --水量r.hx_freceivablefee hx_freceivablefee, --水费r.hx_fcollchargesreceivable4 wsf --污水费from hx_t_waterusedamount w --水量INNER JOIN Team t --团队	ON w.OwningTeam = t.TeamId  AND isnull(w.OwningTeam,'00000000-0000-0000-0000-000000000000') = isnull(isnull(@TeamId,w.OwningTeam),'00000000-0000-0000-0000-000000000000')AND [email protected] and [email protected] and [email protected] and w.hx_frecordtype='100000001'INNER JOIN hx_t_teamattribution n    ON t.TeamId=n.hx_fteamid AND isnull(n.hx_flevyinstituteid,'00000000-0000-0000-0000-000000000000') = isnull(isnull(@BusinessunitId,n.hx_flevyinstituteid),'00000000-0000-0000-0000-000000000000')inner JOIN #t_estimateamountreason e --估水原因    ON e.Value=w.hx_festimateamountreason left join hx_t_receivable r --应收on w.hx_t_waterusedamountid=r.hx_fusedamountid  UNION ALLselect null fullname,null hx_fzone,1 mcounts,e.Label Label,0 hx_fpayamount,0 hx_freceivablefee,0 wsf from #t_estimateamountreason e) h--参数化动态PIVOT行转列DECLARE @sql_str NVARCHAR(MAX)DECLARE @sql_col NVARCHAR(MAX)DECLARE @tableName SYSNAME --行转列表DECLARE @orderColumn SYSNAME --分组字段DECLARE @row2column SYSNAME --行变列的字段DECLARE @row2columnValue SYSNAME --行变列值的字段DECLARE @OtherField NVARCHAR(100)DECLARE @sql_col_out NVARCHAR(MAX)SET @tableName = '#t_EstimateamountReasonTotal'SET @orderColumn = 'pvt.hx_fzone'SET @row2column = 'Label'SET @row2columnValue = 'mcounts'SET @OtherField='fullname,hx_fzone,hx_fpayamount,hx_freceivablefee,wsf'--从行数据中获取可能存在的列SET @sql_str = N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['[email protected]+'])     FROM ['[email protected]+'] GROUP BY ['[email protected]+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@[email protected]_col OUTPUT--PRINT @sql_colSET @sql_str = N'SELECT * FROM (    SELECT '[email protected]+',['[email protected]+'],['[email protected]+']'+'FROM ['[email protected]+']) p PIVOT     (sum(['[email protected]+']) FOR ['[email protected]+'] IN ( '+ @sql_col +') ) AS pvt where pvt.hx_fzone is not null ORDER BY '[email protected]--PRINT (@sql_str)EXEC (@sql_str)drop table #t_estimateamountreasondrop table #t_EstimateamountReasonTotalend---------------------------------------------------------------------------select @end_date = getdate()select datediff(ms,@begin_date,@end_date) as '用时/毫秒'

  相关解决方案