MSSQL 使用pivot动态行转列后出现字段值为NULL的处理方法

最近在写报表取数，涉及到行转列，过程中参考博客园 About 的文章（SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）），写得好，容易理解，也能很快上手。

因为涉及到动态列的问题，当原始数据集记录，不存在pivot(数据集)中某列时，转换后就会出现null的字段，虽然程序中能做相应处理，但总归不爽。于是有了一下的处理。

逻辑是，在进行最后pivot转换前，对原数据集进行补充“不相干”(也就是不影响后期统计的数据，我这里是添加默认值为0的记录)的数据，目的是覆盖所有的转换后的字段，避免出现null。‘

可能字面很难理解，我描述也有不到位的，还是sql脚本吧

----创建临时表

Create Table #ProjectTemp(
LevelIndex int ,
ProjectGuid UNIQUEIDENTIFIER,
ProjectName NVARCHAR(100),
OrgStructureName NVARCHAR(100),
AreaName NVARCHAR(100),
GategoryName NVARCHAR(100)
)

----保存经过权限过滤的项目

INSERT INTO #ProjectTemp(LevelIndex,ProjectGuid,ProjectName,OrgStructureName,AreaName,GategoryName) EXEC sp_GetProjectByUserRight @uid

 

----动态行转列

------获取所有列

SET @AreaSql = STUFF((
SELECT ',' + QUOTENAME(AreaName)
FROM (
SELECT DISTINCT AreaName
FROM #ProjectTemp
) TEMP
ORDER BY AreaName
FOR XML PATH('')
), 1, 1, '')

SET @sql = 'select *from (select count(0) as Total ,Area from cha_HB_AllplanRec_Test
where exists(select 0 from #ProjectTemp where cha_AllplanTest.Area= #ProjectTemp.AreaName )
group by Area ) temp
pivot (sum(Total)for Area in (' + @AreaSql + ')) a'
EXEC (@sql)

得到的结果是这样：

华东区域	华北区域	华南区域	华中区域	集团
465	377	129	283	NULL

 

集团出现null的原因是，#ProjectTemp表中，有属于集团的项目记录，但是业务数据表cha_AllplanTest中没有集团项目的数据。

 

----创建临时表

Create Table #ProjectTemp(
LevelIndex int ,
ProjectGuid UNIQUEIDENTIFIER,
ProjectName NVARCHAR(100),
OrgStructureName NVARCHAR(100),
AreaName NVARCHAR(100),
GategoryName NVARCHAR(100),
DefaultCount int not null default(0) ----调整点1
)

----保存经过权限过滤的项目

INSERT INTO #ProjectTemp(LevelIndex,ProjectGuid,ProjectName,OrgStructureName,AreaName,GategoryName) EXEC sp_GetProjectByUserRight @uid

 

----动态行转列

------获取所有列

SET @AreaSql = STUFF((
SELECT ',' + QUOTENAME(AreaName)
FROM (
SELECT DISTINCT AreaName
FROM #ProjectTemp
) TEMP
ORDER BY AreaName
FOR XML PATH('')
), 1, 1, '')

SET @sql = 'select *from (select count(0) as Total ,Area from cha_HB_AllplanRec_Test 
where exists(select 0 from #ProjectTemp where cha_AllplanTest.Area= #ProjectTemp.AreaName )
group by Area
UNION ALL
select DefaultCount as Total ,AreaName as Area from #ProjectTemp) temp   ----调整点2
pivot (sum(Total)for Area in (' + @AreaSql + ')) a'
EXEC (@sql)

执行结果：

华东区域	华北区域	华南区域	华中区域	集团
465	377	129	283	0

对转换前的数据集补充了DefaultCount为0的集团项目记录，在转换中，使用sum函数，不影响最后结果。算是解决了null的问题。

 

参考：https://www.cnblogs.com/gaizai/p/3753296.html