请高手帮忙分析下 行转列的存储过程（附源码）

Question

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,COLLECTDATETIMEOPEN authors_cursorFETCH NEXT FROM authors_cursor 
INTO @MEASURESETID, @COLLECTDATETIME,@INCEPTDATETIME,@DATADICTIONARYSYMBOL,@RAWDATAWHILE @@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_cursorINSERT INTO dbo.measureSetRaw
SELECT * FROM #TEMPDROP TABLE #TEMP
END
GO目标是从多行表DBO.MEASURESETRAWDATA 中把数据转存到多列表dbo.measureSetRaw

Answer 1

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

Answer 2

普通行列转换假设有张学生成绩表(t)如下Name Subject Result
张三 语文　　73
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94想变成 
姓名 语文 数学 物理
张三 73　　83　　93
李四 74　　84　　94create table #t
(
   Name    varchar(10) ,
   Subject varchar(10) ,
   Result  int
)insert into #t(Name , Subject , Result) values('张三','语文','73')
insert into #t(Name , Subject , Result) values('张三','数学','83')
insert into #t(Name , Subject , Result) values('张三','物理','93')
insert into #t(Name , Subject , Result) values('李四','语文','74')
insert into #t(Name , Subject , Result) values('李四','数学','83')
insert into #t(Name , Subject , Result) values('李四','物理','93')declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']'
from (select distinct Subject from #t) as a
set @sql = @sql + ' from #t group by name'
exec(@sql) drop table #t--结果
姓名       数学        物理        语文          
---------- ----------- ----------- ----------- 
李四       83          93          74
张三       83          93          73
 
----------------------------------------------------
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 73　　83　　93
李四 74　　84　　94想变成 
Name Subject Result
张三 语文　　73
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94create table #t
(
   姓名 varchar(10) ,
   语文 int ,
   数学 int ,
   物理 int
)insert into #t(姓名 , 语文 , 数学 , 物理) values('张三',73,83,93)
insert into #t(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select 姓名 as Name,'语文' as Subject,语文 as Result from #t union
select 姓名 as Name,'数学' as Subject,数学 as Result from #t union
select 姓名 as Name,'物理' as Subject,物理 as Result from #t
order by 姓名 desc drop table #t
 
--结果
Name       Subject Result      
---------- ------- ----------- 
张三       数学    83
张三       物理    93
张三       语文    73
李四       数学    84
李四       物理    94
李四       语文    74(所影响的行数为 6 行)

Answer 3

--带符号合并行列转换--有表t，其数据如下：
  a b
  1 1
  1 2
  1 3
  2 1
  2 2
  3 1
--如何转换成如下结果:
  a b
  1 1,2,3
  2 1,2
  3 1 create table tb
(
   a int,
   b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
goif object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(b as varchar) from tb where a = @a 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a           b     
----------- ------
1           1,2,3
2           1,2
3           1（所影响的行数为 3 行）
多个前列的合并
数据的原始状态如下：
ID  PR   CON  OP    SC 
001 p    c    差    6
001 p    c    好    2
001 p    c    一般  4
002 w    e    差    8
002 w    e    好    7
002 w    e    一般  1
===========================
用SQL语句实现，变成如下的数据
ID  PR   CON  OPS
001 p    c    差(6),好(2),一般(4)
002 w    e    差(8),好(7),一般(1)if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)
 
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '差',    6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '好',    2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '一般',  4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '差',    8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '好',    7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '一般',  1)
goif object_id('pubs..test') is not null
   drop table test
go
select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb--创建一个合并的函数
if object_id('pubs..f_hb') is not null
   drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from testdrop table tb
drop table test--结果
id         pr         con        OPS                
---------- ---------- ---------- -------------------
001        p          c          差(6),好(2),一般(4)
002        w          e          差(8),好(7),一般(1)（所影响的行数为 2 行）create table b
(col varchar(20))insert b values ('a')
insert b values ('b')
insert b values ('c')
insert b values ('d')
insert b values ('e')
declare @sql varchar(1024)
set @sql=''
select @sql=@sql+b.col+',' from (select col from b) as b
set @sql='select '''+@sql+''''
exec(@sql)

Answer 4

行列转换并取平均值站名 年份   数据
AA    1986  23.5
AA    1987  25.6
AA    1988  23.0
BB    1986  21.0
BB    1987  22.0
BB    1988  22.7
CC    1986  21.8
CC    1987  23.0
CC    1988  21.8
......我想写成这样的表：
 序号   年份      AA       BB      CC      ....平均
 1      1986      23.5     21.0    21.8    ....22.1    
 2      1987      25.6     22.0    23.0    ....23.5
 3      1988      23.0     22.7    21.8    ....22.5
create table #tab ([站名][char](10),[年份] [int],[数据] [decimal](18, 1))
insert #tab values('AA',1986,23.5)
insert #tab values('AA',1987,25.6)
insert #tab values('AA',1988,23.0)
insert #tab values('BB',1986,21.0)
insert #tab values('BB',1987,22.0)
insert #tab values('BB',1988,22.7)
insert #tab values('CC',1986,21.8)
insert #tab values('CC',1987,23.0)
insert #tab values('CC',1988,21.8)declare @sql varchar(4000)
set @sql = 'select 年份 '
select @sql = @sql + ' , sum(case 站名 when '''+ 站名 +''' then 数据 end) ['+ 站名 +']'
from (select distinct 站名 from #tab) as a
set @sql = @sql + ', avg(数据) as 平均 from #tab group by 年份'
exec(@sql) drop table #tab年份 AA    BB    CC    平均                                       
---- ----- ----- ----- ----------
1986 23.5  21.0  21.8  22.100000
1987 25.6  22.0  23.0  23.533333
1988 23.0  22.7  21.8  22.500000
--另外的做法
declare @sql varchar(8000)
set @sql='select 年份'
select @sql=@sql+',['+站名+']=max(case 站名 when '''+站名+''' then 数据 end)' from 表 group by 站名 order by 站名
set @sql=@sql+',avg(数据) as 平均 from 表 group by 年份'
exec(@sql)declare @name varchar(1000)
set @name=''
select @name=@name+',max(case when 站名='''+站名+''' then 數據 end) ['+站名+']' from test group by 站名 order by 站名
select @name='select identity(int,1,1)序號,年份'+@name+',cast(avg(數據) as decimal(15,1)) 平均 into #a from test group by 年份 order by 1 select * from #a'
exec (@name)
/*
序號 年份 AA BB CC 平均
1 1986 23.5 21.0 21.8 22.1
2 1987 25.6 22.0 23.0 23.5
3 1988 23.0 22.7 21.8 22.5
*/

Answer 5

原来表                              
 编号  类型 结果                       编号   9011    9012    9013
 a      9011  4300             
 a      9012  2300
 a      9013  1500

Answer 6

看原來的存儲過程，好象只有這麼幾種情況，就可以不用動態語句實現。
Select
编号,
SUM(Case 类型 When '9010' Then 结果 Else 0 End) As [9010],
SUM(Case 类型 When '9020' Then 结果 Else 0 End) As [9020],
SUM(Case 类型 When '9110' Then 结果 Else 0 End) As [9110],
SUM(Case 类型 When '9120' Then 结果 Else 0 End) As [9120]
From
TableName
Group By 编号

Answer 7

不要用游标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

Answer 8

原来表                              
 编号  类型 结果                       编号   9011    9012    9013
 a      9011  4300             
 a      9012  2300
 a      9013  1500declare @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)

Answer 9

if object_id('pubs..tb') is not null
   drop table tb
gocreate 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)
godeclare @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
*/