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

运行效果如下所示：