实现下图类似效果统计
数据库设计如下
存储过程如下所示:
USE [DBTEST] GO /****** Object: StoredProcedure [dbo].[GetData] Script Date: 2023-09-01 16:56:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[GetData] as declare @className VARCHAR(50) declare @titleSql varchar(8000) declare @contentSql varchar(8000) declare @allSql varchar(8000) ----------------------------------------------------生成表头---------------------------------------------------- set @titleSql= 'select ' +'''检查医生'',' --1.声明游标 DECLARE cursor_devices CURSOR FOR select classname from test group by ClassName --2.打开游标 OPEN cursor_devices; --3.读取游标数据 FETCH NEXT FROM cursor_devices INTO @className; --判断是否执行成功,0 表示成功;-1表示失败 WHILE @@FETCH_STATUS = 0 BEGIN set @titleSql = @titleSql +''''+ @className + ''',' --再次读取,否则只读取一次 FETCH NEXT FROM cursor_devices INTO @className; END; set @titleSql= LEFT(@titleSql,len(@titleSql)-1) print '@titleSql==='+@titleSql; --4.关闭游标 CLOSE cursor_devices; --5.释放游标 DEALLOCATE cursor_devices; ----------------------------------------------------生成表数据---------------------------------------------------- --行转列 --select ---- ROW_NUMBER() over (order by doctorname) as rownumber, -- doctorname, -- sum(case when classname ='CT' then checkcount end) as 'CT', --else 0 可省略 -- sum(case when classname ='MR' then checkcount end) as 'MR', --else 0 可省略 -- sum(case when classname ='DR' then checkcount end) as 'DR' --else 0 可省略 --from test group by doctorname set @contentSql= 'select doctorname as 检查医生, ' --1.声明游标 DECLARE cursor_devices2 CURSOR FOR select classname from test group by ClassName --2.打开游标 OPEN cursor_devices2; --3.读取游标数据 FETCH NEXT FROM cursor_devices2 INTO @className; --判断是否执行成功,0 表示成功;-1表示失败 WHILE @@FETCH_STATUS = 0 BEGIN set @contentSql = @contentSql +'convert(varchar,sum(case when classname ='+''''+ @className + ''' then checkcount end)) as' +''''+ @className + ''',' --再次读取,否则只读取一次 FETCH NEXT FROM cursor_devices2 INTO @className; END; set @contentSql= LEFT(@contentSql,len(@contentSql)-1) + ' from test group by doctorname' print @contentSql; set @allSql=@titleSql +' union all '+ @contentSql print @contentSql; exec(@allSql); --4.关闭游标 CLOSE cursor_devices2; --5.释放游标 DEALLOCATE cursor_devices2; --exec GetData
运行效果如下所示:
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!