首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

请高手帮忙分析下 行转列的存储过程(附源码)解决方案

2012-01-31 
请高手帮忙分析下 行转列的存储过程(附源码)CREATEPROCEDUREDUMP_GDDATA(@Dailysmalldatetime)ASBEGINCREA

请高手帮忙分析下 行转列的存储过程(附源码)
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
*/

热点排行