018 数据库学习笔记-- 实现二维表头统计(存储过程+游标+行转列+字符串截取)

实现下图类似效果统计

数据库设计如下

存储过程如下所示：

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