sybase 带参数存储过程实现行转列

创建一个存储过程，用于处理行专列问题
USE 数据库名称
– USE sjk
GO
– 创建临时表
IF OBJECT_ID(‘#TEMP_MC’) IS NOT NULL
drop table #TEMP_MC
GO

CREATE TABLE #TEMP_MC (
DSR_BH char(32),
DSR_MC varchar(300),
nums int
);
GO
– 存储过程如果存在先删除
IF OBJECT_ID(‘YWST…PR_INIT_CTRY’) IS NOT NULL
BEGIN
DROP PROCEDURE PR_INIT_CTRY
END
GO

CREATE PROCEDURE PR_INIT_CTRY (@tName varchar(100) )
AS
BEGIN
DECLARE @SELECT_SQL varchar(3000)
– 待处理的 出庭人员信息

-- 将基础数据 插入临时表
SET @SELECT_SQL = 'INSERT INTO #TEMP_MC ( DSR_BH, DSR_MC)
	select mct.C_BH DSR_BH, C_MC DSR_MC
	FROM mct  where mct.C_MC is not null and mct.C_BH is not null'
 -- cs
 if exists ( select 1 from ( select 'CS' name ) tab where name = @tName)
   SET @SELECT_SQL = ''
EXECUTE(@SELECT_SQL)

--  组装名称 ,将出庭人员姓名组装成  xx;xx;xx;xx 
declare @state varchar(400)
declare @id VARCHAR(32)
declare @count int
set @state = ''
set @count = 0  
update #TEMP_MC
set DSR_MC=(case when @id = DSR_BHthen @state || ';' || DSR_MC
else DSR_MC end) , 
@state =(case when @id = DSR_BH then @state || ';' || DSR_MC
else DSR_MC end) , 
nums =(case when @id = DSR_BH then @count + 1
else 1 end) , 
@count =(case when @id = DSR_BH then @count + 1 else 1 end) , 
@id = DSR_BH

– 使用游标 循环 临时表，
– 声明游标变量
DECLARE @KTBH varchar(300)

-- 声明游标并打开
DECLARE fyidCursor CURSOR FOR  select DISTINCT  ktbh  from YWST.dbo.#TEMP_MC 

OPEN fyidCursor

-- 获取第一个元素
FETCH NEXT FROM fyidCursor INTO @KTBH

DECLARE @INSERT_SQL varchar(3000)

-- 循环处理元素
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 处理当前元素
    -- 处理数据
	SET @INSERT_SQL = ''
	-- 打印sql，调试的时候开

– EXEC PRINTSQL @INSERT_SQL
– 执行sql
EXECUTE(@INSERT_SQL)
– 获取下一个元素
FETCH NEXT FROM fyidCursor INTO @KTBH
END
– 关闭游标
CLOSE fyidCursor
DEALLOCATE fyidCursor

 -- 将这个类型的案件数据清空
EXECUTE('delete From  #TEMP_MC')

END
GO

– 执行存储过程
exec PR_INIT_CTRY ‘PT’
exec PR_INIT_CTRY ‘CS’

GO

– 删除存储过程
DROP PROCEDURE PR_INIT_CTRY
GO
drop table #TEMP_MC
GO