sqlserver报表统计――参数化动态PIVOT行转列(二)
所(零散查表组)
-----------------SQL正文----------------------------------------------------------执行时间：
declare @FMonth int
declare @FYear int
declare @FTimes int
select @FYear=t.hx_FYear,@FMonth=t.hx_FMonth,@FTimes=t.hx_FTimes from hx_ClosingAccountInfo t where hx_ClosingAccountInfoId=@hx_ClosingAccountInfoId
--select @FYear,@FMonth,@FTimes
begin
if object_id('tempdb..#t_estimateamountreason') is not null drop table #t_estimateamountreason
if object_id('tempdb..#t_EstimateamountReasonTotal') is not null drop table #t_EstimateamountReasonTotal
select * 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) t
select * 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 w.hx_FYear=@FYear and w.hx_FMonth=@FMonth and w.hx_FTimes=@FTimes 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 ALL
select 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(['+@row2column+'])
FROM ['+@tableName+'] GROUP BY ['+@row2column+']'
--PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col
SET @sql_str = N'
SELECT * FROM (
SELECT '+@OtherField+',['+@row2column+'],['+@row2columnValue+']'+'FROM ['+@tableName+']) p PIVOT
(sum(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt
where pvt.hx_fzone is not nul