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