SQLServer 复杂报表实现--动态生成列名

报表要求如下：

数据源：

表：

CREATE TABLE KangDR.dbo.t_BaseLine_SharpInstrument (
FID INT NOT NULL
,FHospitalID INT NULL
,FUserName VARCHAR(20) NULL
,FYearMonth VARCHAR(20) NULL
,FInjuryTimes INT NULL
,FOperatorID INT NULL
,FDate DATETIME NULL
,FSex INT NULL
,FWorkAge DECIMAL(4, 2) NULL
,FWorkTypeID INT NULL
,FTrain INT NULL
,FAmpouleBottleBurstTimes INT NULL
,FAmpouleBottleBurstType INT NULL
,FSource INT NOT NULL DEFAULT (0)
,FDeptID INT NOT NULL DEFAULT (0)
,CONSTRAINT PK_T_BN_BASELINESHARPINSTRUMEN PRIMARY KEY CLUSTERED (FID)
) ON [PRIMARY]
GO

CREATE TABLE KangDR.dbo.t_BaseLine_SharpInstrumentDetial (
FID INT NULL
,FParentID INT NULL
,FSourceExposure INT NULL
,FPollutionStatus INT NULL
,FBloodBorneDiseases INT NULL
,FDiseaseType INT NULL
,FOrtherDiseaseType VARCHAR(50) NULL
,FReported INT NULL
,FHappenType INT NULL
,FHappenPlaces INT NULL
,FInstrumentID INT NULL
,FHappenOrtherType VARCHAR(200) NULL
,FHappenOrtherPlaces VARCHAR(200) NULL
,FOrtherInstrument VARCHAR(200) NULL
) ON [PRIMARY]
GO

视图：

SELECT
main.FID
,main.FHospitalID
,main.FUserName
,main.FYearMonth
,main.FInjuryTimes
,main.FOperatorID
,main.FDate
,main.FSex
,main.FWorkAge
,main.FWorkTypeID
,main.FTrain
,main.FAmpouleBottleBurstTimes
,u.FName AS FOperatorName
,main.FAmpouleBottleBurstType
,hos.FProvinceID
,hos.FTownID
,hos.FDistrictID
,hos.FName AS FHospitalName
,main.FSource
,main.FDeptID
,dept.FName FDeptName
FROM dbo.t_BaseLine_SharpInstrument main
left JOIN dbo.t_Base_User u ON main.FOperatorID = u.FID
INNER JOIN dbo.v_Item_Hospital hos ON main.FHospitalID = hos.FItemID
LEFT JOIN v_Item_BaselineDept dept ON dept.FItemID = main.FDeptID

SELECT
head.FID
,head.FParentID
,head.FSourceExposure
,head.FPollutionStatus
,head.FBloodBorneDiseases
,head.FDiseaseType
,head.FOrtherDiseaseType
,head.FReported
,head.FHappenType
,head.FHappenPlaces
,head.FInstrumentID
,head.FHappenOrtherType
,head.FHappenOrtherPlaces
,head.FOrtherInstrument
,instrument.FName AS FInstrumentName
,place.FName AS FHappenPlacesName
,disease.FName AS FDiseaseTypeName
,happentype.FName AS FHappenTypeName
FROM dbo.t_BaseLine_SharpInstrumentDetial head
LEFT OUTER JOIN dbo.t_Item instrument
ON instrument.FItemID = head.FInstrumentID
AND instrument.FItemClassID = 61
LEFT OUTER JOIN dbo.t_Item place
ON head.FHappenPlaces = place.FItemID
AND place.FItemClassID = 60
LEFT OUTER JOIN dbo.t_Item happentype
ON head.FHappenType = happentype.FItemID
AND happentype.FItemClassID = 59
LEFT OUTER JOIN dbo.t_Item disease
ON head.FDiseaseType = disease.FItemID

实现代码：

-- =============================================
-- Author: zwz
-- Create date: 2017-12-21 16:51:40
-- Database: KangDR
-- Description: 暴露源感染--常见操作流程
-- =============================================

CREATE PROCEDURE dbo.p_BaseLine_SharpHappenDiseaseTypeReport
@UserID INT
,@Where VARCHAR(2000) = ''
AS

CREATE TABLE #DataSource
(
FID INT
,FHospitalID INT
,FUserName VARCHAR(200)
,FYearMonth VARCHAR(200)
,FInjuryTimes INT
,FOperatorID INT
,FDate DATETIME
,FSex INT
,FWorkAge INT
,FWorkTypeID INT
,FTrain INT
,FAmpouleBottleBurstTimes INT
,FOperatorName VARCHAR(200)
,FAmpouleBottleBurstType INT
,FProvinceID INT
,FTownID INT
,FDistrictID INT
,FHospitalName VARCHAR(200)
,FSource INT
,FDeptID INT
,FDeptName VARCHAR(200)
,FHappenTypeName VARCHAR(200)
,类型 VARCHAR(200)
)
DECLARE @sql VARCHAR(MAX)=''
SELECT @sql=
'INSERT INTO #DataSource
SELECT
m.FID
,m.FHospitalID
,m.FUserName
,m.FYearMonth
,m.FInjuryTimes
,m.FOperatorID
,m.FDate
,m.FSex
,m.FWorkAge
,m.FWorkTypeID
,m.FTrain
,m.FAmpouleBottleBurstTimes
,m.FOperatorName
,m.FAmpouleBottleBurstType
,m.FProvinceID
,m.FTownID
,m.FDistrictID
,m.FHospitalName
,m.FSource
,m.FDeptID
,m.FDeptName
,detail.FHappenTypeName
,detail.FDiseaseTypeName
FROM v_BaseLine_SharpInstrument m
INNER JOIN t_Item t
ON m.FWorkTypeID = t.FItemID
inner JOIN v_BaseLine_SharpInstrumentDetial detail
ON detail.FParentID = m.FID
WHERE 1 = 1 '

IF (ISNULL(@Where, '') <> '')
SELECT
@sql += @Where
EXEC (@sql);

declare @sqlculomn varchar(8000),@str VARCHAR(8000)
select @sqlculomn = isnull(@sqlculomn + ',' , '') + '[' + FHappenTypeName + ']' from #DataSource group by FHappenTypeName

declare @sqlsetnull varchar(8000)
select @sqlsetnull = isnull(@sqlsetnull + ',' , '') + '[' + FHappenTypeName + ']=isnull(' + + '[' + FHappenTypeName + ']' + ',0)' from #DataSource group by FHappenTypeName

DECLARE @sqlsumculomn VARCHAR(8000)
select @sqlsumculomn = isnull(@sqlsumculomn + ',' , '') + 'SUM([' + FHappenTypeName + '])' from #DataSource group by FHappenTypeName

DECLARE @sqlsumrow VARCHAR(8000)
SELECT @sqlsumrow = isnull(@sqlsumrow + '+' , '') + '[' + FHappenTypeName + ']' from #DataSource group by FHappenTypeName

declare @sqlculomnrate varchar(8000)
select @sqlculomnrate = ISNULL(@sqlculomnrate+ ',' , '') + '[' + FHappenTypeName + '率] DECIMAL(8,2) not null default 0' from #DataSource group by FHappenTypeName

DECLARE @sqlrate varchar(8000)
select @sqlrate = isnull(@sqlrate + ',' , '') + '[' + FHappenTypeName + '率] =' + ' cast([' + FHappenTypeName + '] as DECIMAL(8,2))/cast([总计] as DECIMAL(8,2))' from #DataSource group by FHappenTypeName

--行列转置
SET @str = ('select m.* into ##tmp from
(select * from (select FHappenTypeName,类型,COUNT(*) FNum from #DataSource
GROUP BY FHappenTypeName,类型) a
pivot (max(FNum) for FHappenTypeName in (' + @sqlculomn + ')) b) m ')
EXEC(@str)

--把null转换成0
SET @sqlsetnull='update ##tmp set ' + @sqlsetnull
--PRINT(@sqlsetnull)
EXEC (@sqlsetnull)

--合计列
SET @sqlsumculomn = 'INSERT INTO ##tmp ([类型],'+ @sqlculomn +') SELECT ''总计'',' + @sqlsumculomn + ' FROM ##tmp'
exec (@sqlsumculomn)

--合计行
ALTER TABLE ##tmp add 总计 INT NOT NULL DEFAULT 0
SET @sqlsumrow = 'UPDATE ##tmp SET 总计='+ @sqlsumrow
exec (@sqlsumrow)

--生成率列
SET @sqlculomnrate = 'ALTER TABLE ##tmp ADD ' + @sqlculomnrate
EXEC (@sqlculomnrate)
--计算率
SET @sqlrate = 'UPDATE ##tmp SET ' + @sqlrate
EXEC (@sqlrate)

SELECT * FROM ##tmp

DROP TABLE ##tmp
DROP TABLE #DataSource

GO

SQLServer 复杂报表实现--动态生成列名

相关推荐