请高手帮忙分析下 行转列的存储过程（附源码）
CREATE PROCEDURE DUMP_GDDATA
(
@Daily smalldatetime
)
AS
BEGIN
CREATE TABLE #TEMP(
MeasureSetID nvarchar (4) NOT NULL ,
CollectDateTime smalldatetime NOT NULL ,
InceptDateTime smalldatetime NOT NULL ,
RawData9010 numeric(18, 4) NULL ,
RawData9020 numeric(18, 4) NULL ,
RawData9110 numeric(18, 4) NULL ,
RawData9120 numeric(18, 4) NULL ,
)
DECLARE @MEASURESETID NVARCHAR(4)
DECLARE @COLLECTDATETIME DATETIME
DECLARE @INCEPTDATETIME DATETIME
DECLARE @DATADICTIONARYSYMBOL NVARCHAR(4)
DECLARE @RAWDATA NUMERIC(18,4)

DECLARE authors_cursor CURSOR FOR
SELECT CAST(MEASURESETID AS nvarchar) AS MEASURESETID,COLLECTDATETIME,INCEPTDATETIME,DATADICTIONARYSYMBOL,RAWDATA
FROM DBO.MEASURESETRAWDATA
WHERE DATEDIFF(DAY,COLLECTDATETIME,@Daily)=0
--WHERE DATEDIFF(DAY,COLLECTDATETIME,@DATETIME)=0
ORDER BY MEASURESETID,COLLECTDATETIME

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @MEASURESETID, @COLLECTDATETIME,@INCEPTDATETIME,@DATADICTIONARYSYMBOL,@RAWDATA

WHILE @@FETCH_STATUS = 0

BEGIN

IF @DATADICTIONARYSYMBOL= '9010 '
INSERT INTO #TEMP(MeasureSetID,CollectDateTime,INCEPTDATETIME,RawData9010)
VALUES(@MEASURESETID,@COLLECTDATETIME,@INCEPTDATETIME,@RAWDATA)

IF @DATADICTIONARYSYMBOL= '9020 '
INSERT INTO #TEMP(MeasureSetID,CollectDateTime,INCEPTDATETIME,RawData9020)
VALUES(@MEASURESETID,@COLLECTDATETIME,@INCEPTDATETIME,@RAWDATA)

IF @DATADICTIONARYSYMBOL= '9110 '
INSERT INTO #TEMP(MeasureSetID,CollectDateTime,INCEPTDATETIME,RawData9110)
VALUES(@MEASURESETID,@COLLECTDATETIME,@INCEPTDATETIME,@RAWDATA)

IF @DATADICTIONARYSYMBOL= '9120 '
INSERT INTO #TEMP(MeasureSetID,CollectDateTime,INCEPTDATETIME,RawData9120)
VALUES(@MEASURESETID,@COLLECTDATETIME,@INCEPTDATETIME,@RAWDATA)

FETCH NEXT FROM authors_cursor
INTO @MEASURESETID, @COLLECTDATETIME,@INCEPTDATETIME, @DATADICTIONARYSYMBOL,@RAWDATA
END

CLOSE authors_cursor

INSERT INTO dbo.measureSetRaw
SELECT * FROM #TEMP

DROP TABLE #TEMP
END
GO

目标是从多行表DBO.MEASURESETRAWDATA 中把数据转存到多列表dbo.measureSetRaw

[解决办法]
游标存在的错误,是因为你关闭了，但是没有释放
CLOSE authors_cursor 后还得deallocate authors_cursor

[解决办法]
不要用游标

Select Cast(MEASURESETID As nvarchar) As MEASURESETID,
COLLECTDATETIME,
INCEPTDATETIME,
case when DATADICTIONARYSYMBOL= '9010 ' then '9010 'else null end as RawData9010,
case when DATADICTIONARYSYMBOL= '9020 ' then '9020 'else null end as RawData9020,
case when DATADICTIONARYSYMBOL= '9110 ' then '9110 'else null end as RawData9110,
case when DATADICTIONARYSYMBOL= '9120 ' then '9120 'else null end as RawData9120,
RAWDATA
From MEASURESETRAWDATA
Where DATEDIFF(DAY,COLLECTDATETIME,@Daily)=0
and DATADICTIONARYSYMBOL in( '9010 ', '9020 ', '9110 ', '9120 ')

Order By MEASURESETID,COLLECTDATETIME
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(编号 varchar(10),类型 varchar(10),结果 int)
insert into tb(编号,类型,结果) values( 'a ', '9011 ', 4300)
insert into tb(编号,类型,结果) values( 'a ', '9012 ', 2300)
insert into tb(编号,类型,结果) values( 'a ', '9013 ', 1500)
go

declare @sql varchar(8000)
set @sql = 'select 编号 '
select @sql = @sql + ' , sum(case 类型 when ' ' ' + 类型 + ' ' ' then 结果 else null end) [ ' + 类型 + '] '
from (select distinct 类型 from tb) as a
set @sql = @sql + ' from tb group by 编号 '
exec(@sql)

drop table tb
/*
编号 9011 9012 9013
---------- ----------- ----------- -----------
a 4300 2300 1500
*/