SQL行列互换

最新推荐文章于 2024-02-27 16:07:08 发布

luyuncsd123

最新推荐文章于 2024-02-27 16:07:08 发布

阅读量1.1k

点赞数

文章标签： SQL

面试的时候遇到的数据库SQL问题，没写过，然后研究了一下，现将结果记录下来，方便以后查询。

题目1：将表tbltest1的行列互换

表结构：

student     kemu fenshu
student1    语文    80
student1    数学    90
student1    英语    85
student2    语文    85
student2    数学    92
student2    英语    82

变成：
student 语文    数学   英语
student1   80     90     85
student2   85     92     82

SQLserver的sql语句：

declare @sql varchar(4000)
set @sql = 'select student'
select @sql = @sql + ',sum(case kemu when '''+ kemu + ''' then fenshu else 0 end)['+ kemu+ ']'
   from ( select distinct kemu from tbltest1) as a
set @sql = @sql + ' from tbltest1 group by student'
exec(@sql)

或者

select student, sum( case kemu when '语文' then fenshu else 0 end) 语文, sum( case kemu when '数学' then fenshu else 0 end) 数学, sum( case kemu when '英语' then fenshu else 0 end) 英语 from tbltest group by student

注：个人觉得上面的好。如果一两个选项可以使用下面的sql，如果选项多上面的sql就显的方便的多。

2005的话好像还有个函数可以用，等研究好了再发上来。

题目2：合并

表结构tbltest2：

id strings
1   my
1   name
1   is

1   xudayu
2   hello
2   world

转化成：
id   strings
1   my name is xudayu
2   hello world

SQLServer的sql语句：

--创建一个合并的函数
create function fliehebin(@id int)
returns varchar(5000)
as
begin
declare @ str varchar(5000)
set @ str= ''
select @ str=@ str + cast(strings as varchar(50)) + ' ' from tbltest2 where id=@id
set @ str= subString(@ str,1, len(@ str))
return(@ str)
end
go
--调用自定义函数得到结果
select distinct id,dbo.fliehebin(id) from tbltest2

=====================================================================

传说通用的，如下：

[c-sharp] view plain copy print ?

IF EXISTS( select * from sysobjects where xtype='U'and name ='data2' )
Begin
Drop table data2
End
CREATE TABLE [data2] (
[人员编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[基本工资] [numeric](18, 2) NULL ,
[奖金] [numeric](18, 2) NULL ,
[合计] [numeric](19, 2) NULL ,
CONSTRAINT [PK_data2] PRIMARY KEY CLUSTERED
(
[人员编号]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert data2 select 'a1',1.00,11.00,111.00
insert data2 select 'a2',2.00,22.00,222.00
insert data2 select 'a3',3.00,33.00,333.00
insert data2 select 'a100',100.00,100.00,100.00
go
drop PROCEDURE AVB_IniTable
go
/*
author:nyb
time :2005/04/22
fixtime :
aim :转置行和列
input :@TableNane
执行：
EXECUTE AVB_IniTable 'data2'
*/
Create PROCEDURE AVB_IniTable
@TableNane varchar(128)
AS
DECLARE @string VARCHAR(8000)
--1 创建View
IF EXISTS( select * from sysobjects where xtype='V'and name ='V_Temp' )
Begin
Drop view V_Temp
End
SELECT @string = ' Create view V_Temp as select * from ' + @TableNane
EXECUTE (@string)
IF EXISTS( select * from sysobjects where xtype='U'and name ='zzTemp' )
Begin
Drop table zzTemp
End
DECLARE @ColumnName VARCHAR(200)
DECLARE @ColumnStr VARCHAR(5000)
select @ColumnStr= ''
select @ColumnStr=@ColumnStr + quotename(rtrim(人员编号)) +'float NULL,' from V_Temp
print @ColumnStr
SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1)
SELECT @string = 'CREATE TABLE zzTemp (列名 varchar(50) NULL,' + @ColumnStr + ') ON [PRIMARY]'
print @string
EXECUTE (@string)
--2插入记录
DECLARE Column_cur SCROLL CURSOR FOR
SELECT name FROM syscolumns WHERE ID=object_id(@TableNane) and name <>'人员编号'
OPEN Column_cur
FETCH FIRST FROM Column_cur into @ColumnName
WHILE (@@fetch_status<>-1)
BEGIN
select @ColumnStr= ''
if @ColumnName = '基本工资'
select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(基本工资,0)) + ''',' from V_Temp
else if @ColumnName = '奖金'
select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(奖金,0)) + ''',' from V_Temp
else if @ColumnName = '合计'
select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(合计,0)) + ''',' from V_Temp
SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1)
select @string = 'insert into zzTemp values(''' + @ColumnName + ''',' + @ColumnStr +')'
execute(@string)
FETCH NEXT FROM Column_cur into @ColumnName
END
CLOSE Column_cur
DEALLOCATE Column_cur
go
--察看结果
select * from data2
select * from zzTemp

IF   EXISTS(   select     *     from   sysobjects   where   xtype='U'and   name   ='data2'   )   
  Begin   
  Drop   table     data2     
  End     
    
    
  CREATE   TABLE   [data2]   (   
    [人员编号]   [varchar]   (10)   COLLATE   Chinese_PRC_CI_AS   NOT   NULL   ,   
    [基本工资]   [numeric](18,   2)   NULL   ,   
    [奖金]   [numeric](18,   2)   NULL   ,   
    [合计]   [numeric](19,   2)   NULL   ,   
    CONSTRAINT   [PK_data2]   PRIMARY   KEY     CLUSTERED     
    (   
      [人员编号]   
    )     ON   [PRIMARY]     
  )   ON   [PRIMARY]   
  GO   
    
  insert   data2   select   'a1',1.00,11.00,111.00   
  insert   data2   select   'a2',2.00,22.00,222.00   
  insert   data2   select   'a3',3.00,33.00,333.00   
  insert   data2   select   'a100',100.00,100.00,100.00   
    
  go   
  drop       PROCEDURE   AVB_IniTable   
  go   
  /*   
  author:nyb   
  time     :2005/04/22   
  fixtime :   
  aim       :转置行和列   
  input   :@TableNane   
  执行：   
  EXECUTE   AVB_IniTable   'data2'   
  */   
    
  Create       PROCEDURE   AVB_IniTable   
  @TableNane   varchar(128)   
    
  AS   
  DECLARE   @string   VARCHAR(8000)   
  --1   创建View   
  IF   EXISTS(   select     *     from   sysobjects   where   xtype='V'and   name   ='V_Temp'   )   
  Begin   
  Drop   view     V_Temp     
  End     
    
  SELECT   @string   =   '   Create   view   V_Temp   as     select   *     from   '   +   @TableNane     
  EXECUTE   (@string)   
    
  IF   EXISTS(   select     *     from   sysobjects   where   xtype='U'and   name   ='zzTemp'   )   
  Begin   
  Drop   table     zzTemp     
  End     
    
    
  DECLARE   @ColumnName   VARCHAR(200)   
  DECLARE   @ColumnStr   VARCHAR(5000)   
  select   @ColumnStr=   ''   
  select   @ColumnStr=@ColumnStr   +   quotename(rtrim(人员编号))   +'float   NULL,'   from   V_Temp     
  print   @ColumnStr   
    
  SET   @ColumnStr   =   left(@ColumnStr,len(@ColumnStr)-1)   
  SELECT   @string   =   'CREATE   TABLE   zzTemp   (列名   varchar(50)   NULL,'   +     @ColumnStr   +   ')   ON   [PRIMARY]'   
  print   @string   
  EXECUTE   (@string)   
    
  --2插入记录   
  DECLARE   Column_cur     SCROLL   CURSOR   FOR   
  SELECT   name   FROM   syscolumns   WHERE   ID=object_id(@TableNane)   and   name   <>'人员编号'   
  OPEN     Column_cur     
  FETCH   FIRST   FROM     Column_cur   into   @ColumnName   
  WHILE   (@@fetch_status<>-1)   
  BEGIN   
  select   @ColumnStr=   ''   
  if   @ColumnName   =   '基本工资'   
  select   @ColumnStr=   @ColumnStr   +   ''''   +   convert(varchar(20),ISNULL(基本工资,0))   +   ''','   from   V_Temp       
  else   if   @ColumnName   =   '奖金'   
  select   @ColumnStr=   @ColumnStr   +   ''''   +   convert(varchar(20),ISNULL(奖金,0))   +   ''','   from   V_Temp       
  else   if   @ColumnName   =   '合计'   
  select   @ColumnStr=   @ColumnStr   +   ''''   +   convert(varchar(20),ISNULL(合计,0))   +   ''','   from   V_Temp       
    
    SET   @ColumnStr   =   left(@ColumnStr,len(@ColumnStr)-1)   
    select   @string   =   'insert   into   zzTemp   values('''   +   @ColumnName   +   ''','   +     @ColumnStr   +')'   
  execute(@string)   
  FETCH   NEXT   FROM     Column_cur   into   @ColumnName   
  END   
  CLOSE   Column_cur   
  DEALLOCATE   Column_cur   
    
    
  go   
  --察看结果   
  select   *   from   data2   
  select   *   from   zzTemp

=====================================================================

[c-sharp] view plain copy print ?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程
将指定的表,按指定的字段进行行列互换
--邹建 2004.04--*/
/*--使用示例
--测试数据
create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说',38.0,59.2
union all select '散文',18.9,30.6
union all select '哲学',16.2,10.2
/*--要求转换结果
性别小说散文哲学
---- ----- ----- -----
男性 38.0 18.9 16.2
女性 59.2 30.6 10.2
（所影响的行数为 2 行）
--*/
--调用存储过程
exec p_zj '表','类别','性别'
--删除测试
drop table 表
--*/
create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'='''+case isnull(@new_fdname,'') when '' then ''
else @new_fdname+'=' end+''''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+cast(['+@fdname+'] as varchar)+'']=''''''+replace(['+name+'],'''','''''''')+'''''''' from ['+@tbname+']'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname
order by colid
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)
exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_zj]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)   
  drop   procedure   [dbo].[p_zj]   
  GO   
    
  /*--行列互换的通用存储过程   
    
  将指定的表,按指定的字段进行行列互换   
    
  --邹建   2004.04--*/   
    
  /*--使用示例   
    
  --测试数据   
  create   table   表(类别   varchar(10),男性   decimal(20,1),女性   decimal(20,1))   
  insert   表   select   '小说',38.0,59.2   
  union   all   select   '散文',18.9,30.6   
  union   all   select   '哲学',16.2,10.2   
    
  /*--要求转换结果   
  性别       小说         散文         哲学           
  ----   -----   -----   -----     
  男性       38.0     18.9     16.2   
  女性       59.2     30.6     10.2   
    
  （所影响的行数为   2   行）   
  --*/   
    
  --调用存储过程   
  exec   p_zj   '表','类别','性别'   
    
  --删除测试   
  drop   table   表   
  --*/   
  create   proc   p_zj   
  @tbname   sysname, --要处理的表名   
  @fdname   sysname, --做为转换的列名   
  @new_fdname   sysname='' --为转换后的列指定列名   
  as   
  declare   @s1   varchar(8000),@s2   varchar(8000)   
  ,@s3   varchar(8000),@s4   varchar(8000),@s5   varchar(8000)   
  ,@i   varchar(10)   
  select   @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'   
  select   @s1=@s1+',@'+@i+'   varchar(8000)'   
  ,@s2=@s2+',@'+@i+'='''+case   isnull(@new_fdname,'')   when   ''   then   ''   
  else   @new_fdname+'='   end+''''''+name+''''''''   
  ,@s3=@s3+'   
  select   @'+@i+'=@'+@i+'+'',[''+cast(['+@fdname+']   as   varchar)+'']=''''''+replace(['+name+'],'''','''''''')+''''''''   from   ['+@tbname+']'   
  ,@s4=@s4+',@'+@i+'=''select   ''+@'+@i   
  ,@s5=@s5+'+''   union   all   ''+@'+@i   
  ,@i=cast(@i   as   int)+1   
  from   syscolumns     
  where   object_id(@tbname)=id   and   name<>@fdname   
  order   by   colid   
    
  select   @s1=substring(@s1,2,8000)   
  ,@s2=substring(@s2,2,8000)   
  ,@s4=substring(@s4,2,8000)   
  ,@s5=substring(@s5,16,8000)   
    
  exec('declare   '+@s1+'   
  select   '+@s2+@s3+'   
  select   '+@s4+'   
  exec('+@s5+')')   
  go

Oracle's:

[java] view plain copy print ?

最近公司项目涉及到统计报表，有关交叉报表部分差不多都有行列转换需求，根据个人开发中的需求，总结了一些例子
1.创建表
create table T1
(
PRODUCTID VARCHAR2(100),
NUM NUMBER
)
;
2.插入测试数据
insert into T1 (PRODUCTID, NUM)
values ('1', 3);
insert into T1 (PRODUCTID, NUM)
values ('2', 12);
insert into T1 (PRODUCTID, NUM)
values ('3', 24);
insert into T1 (PRODUCTID, NUM)
values ('4', 11);
insert into T1 (PRODUCTID, NUM)
values ('5', 51);
insert into T1 (PRODUCTID, NUM)
values ('6', 511);
commit;
3.编写转换函数
CREATE OR REPLACE FUNCTION fn_rs_1
RETURN pkg_getrecord.myrctype
IS
s VARCHAR2 (4000);
CURSOR c1 IS
SELECT ',sum(case when productid!='|| productid || ' then num else 0 end)' || ' "产品' || productid|| '"' c2
FROM t1
group by productid;
r1 c1%ROWTYPE;
list_cursor pkg_getrecord.myrctype;
BEGIN
s := 'select 1 ';
OPEN c1;
LOOP
FETCH c1 INTO r1;
EXIT WHEN c1%NOTFOUND;
s := s || r1.c2;
END LOOP;
CLOSE c1;
s := s || ' from t1 g ';
dbms_output.put_line(s);
OPEN list_cursor FOR s;
RETURN list_cursor;
END fn_rs_1;
4.java测试代码
public class Test {
private String db_url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
private String db_username = "test";
private String db_passwd = "test";
/**
* @param args
*/
public static void main(String[] args) {
Test test=new Test();
try {
test.exec();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void exec() throws SQLException {
Connection conn = getConnection();
if(conn==null){
throw new SQLException("database not connectioned");
}
CallableStatement cstmt = conn.prepareCall("{?=call fn_rs_1}");
cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs=(ResultSet)cstmt.getObject(1);
if(rs.next()){
for(int j=1;j<=rs.getMetaData().getColumnCount();j++){
System.out.print(rs.getMetaData().getColumnName(j)+"/t");
}
}
System.out.println("");
cstmt.execute();
rs=(ResultSet)cstmt.getObject(1);
while(rs.next()){
System.out.print(rs.getString(1)+"/t");
System.out.print(rs.getString(2)+"/t");
System.out.print(rs.getString(3)+"/t");
System.out.print(rs.getString(4)+"/t");
System.out.print(rs.getString(5)+"/t");
System.out.print(rs.getString(6)+"/t");
System.out.print(rs.getString(7)+"/t");
}
cstmt.close();
}
protected Connection getConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
return DriverManager.getConnection(db_url, db_username, db_passwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jianping_shen/archive/2009/04/01/4041169.aspx
面试的时候遇到的数据库SQL问题，没写过，然后研究了一下，现将结果记录下来，方便以后查询。

题目1：将表tbltest1的行列互换

表结构：

student     kemu fenshu
student1    语文    80
student1    数学    90
student1    英语    85
student2    语文    85
student2    数学    92
student2    英语    82

变成：
student 语文    数学   英语
student1   80     90     85
student2   85     92     82

SQLserver的sql语句：

declare @sql varchar(4000)
set @sql = 'select student'
select @sql = @sql + ',sum(case kemu when '''+ kemu + ''' then fenshu else 0 end)['+ kemu+ ']'
   from ( select distinct kemu from tbltest1) as a
set @sql = @sql + ' from tbltest1 group by student'
exec(@sql)

或者

select student, sum( case kemu when '语文' then fenshu else 0 end) 语文, sum( case kemu when '数学' then fenshu else 0 end) 数学, sum( case kemu when '英语' then fenshu else 0 end) 英语 from tbltest group by student

注：个人觉得上面的好。如果一两个选项可以使用下面的sql，如果选项多上面的sql就显的方便的多。

2005的话好像还有个函数可以用，等研究好了再发上来。

题目2：合并

表结构tbltest2：

id strings
1   my
1   name
1   is

1   xudayu
2   hello
2   world

转化成：
id   strings
1   my name is xudayu
2   hello world

SQLServer的sql语句：

--创建一个合并的函数
create function fliehebin(@id int)
returns varchar(5000)
as
begin
declare @ str varchar(5000)
set @ str= ''
select @ str=@ str + cast(strings as varchar(50)) + ' ' from tbltest2 where id=@id
set @ str= subString(@ str,1, len(@ str))
return(@ str)
end
go
--调用自定义函数得到结果
select distinct id,dbo.fliehebin(id) from tbltest2

=====================================================================

传说通用的，如下：
[c-sharp] view plain copy print ?
1. IF EXISTS( select * from sysobjects where xtype='U'and name ='data2' )
2. Begin
3. Drop table data2
4. End
7. CREATE TABLE [data2] (
8. [人员编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
9. [基本工资] [numeric](18, 2) NULL ,
10. [奖金] [numeric](18, 2) NULL ,
11. [合计] [numeric](19, 2) NULL ,
12. CONSTRAINT [PK_data2] PRIMARY KEY CLUSTERED
13. (
14. [人员编号]
15. ) ON [PRIMARY]
16. ) ON [PRIMARY]
17. GO
19. insert data2 select 'a1',1.00,11.00,111.00
20. insert data2 select 'a2',2.00,22.00,222.00
21. insert data2 select 'a3',3.00,33.00,333.00
22. insert data2 select 'a100',100.00,100.00,100.00
24. go
25. drop PROCEDURE AVB_IniTable
26. go
27. /*
28. author:nyb
29. time :2005/04/22
30. fixtime :
31. aim :转置行和列
32. input :@TableNane
33. 执行：
34. EXECUTE AVB_IniTable 'data2'
35. */
37. Create PROCEDURE AVB_IniTable
38. @TableNane varchar(128)
40. AS
41. DECLARE @string VARCHAR(8000)
42. --1 创建View
43. IF EXISTS( select * from sysobjects where xtype='V'and name ='V_Temp' )
44. Begin
45. Drop view V_Temp
46. End
48. SELECT @string = ' Create view V_Temp as select * from ' + @TableNane
49. EXECUTE (@string)
51. IF EXISTS( select * from sysobjects where xtype='U'and name ='zzTemp' )
52. Begin
53. Drop table zzTemp
54. End
57. DECLARE @ColumnName VARCHAR(200)
58. DECLARE @ColumnStr VARCHAR(5000)
59. select @ColumnStr= ''
60. select @ColumnStr=@ColumnStr + quotename(rtrim(人员编号)) +'float NULL,' from V_Temp
61. print @ColumnStr
63. SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1)
64. SELECT @string = 'CREATE TABLE zzTemp (列名 varchar(50) NULL,' + @ColumnStr + ') ON [PRIMARY]'
65. print @string
66. EXECUTE (@string)
68. --2插入记录
69. DECLARE Column_cur SCROLL CURSOR FOR
70. SELECT name FROM syscolumns WHERE ID=object_id(@TableNane) and name <>'人员编号'
71. OPEN Column_cur
72. FETCH FIRST FROM Column_cur into @ColumnName
73. WHILE (@@fetch_status<>-1)
74. BEGIN
75. select @ColumnStr= ''
76. if @ColumnName = '基本工资'
77. select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(基本工资,0)) + ''',' from V_Temp
78. else if @ColumnName = '奖金'
79. select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(奖金,0)) + ''',' from V_Temp
80. else if @ColumnName = '合计'
81. select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(合计,0)) + ''',' from V_Temp
83. SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1)
84. select @string = 'insert into zzTemp values(''' + @ColumnName + ''',' + @ColumnStr +')'
85. execute(@string)
86. FETCH NEXT FROM Column_cur into @ColumnName
87. END
88. CLOSE Column_cur
89. DEALLOCATE Column_cur
92. go
93. --察看结果
94. select * from data2
95. select * from zzTemp
IF EXISTS( select * from sysobjects where xtype='U'and name ='data2' ) Begin Drop table data2 End CREATE TABLE [data2] ( [人员编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [基本工资] [numeric](18, 2) NULL , [奖金] [numeric](18, 2) NULL , [合计] [numeric](19, 2) NULL , CONSTRAINT [PK_data2] PRIMARY KEY CLUSTERED ( [人员编号] ) ON [PRIMARY] ) ON [PRIMARY] GO insert data2 select 'a1',1.00,11.00,111.00 insert data2 select 'a2',2.00,22.00,222.00 insert data2 select 'a3',3.00,33.00,333.00 insert data2 select 'a100',100.00,100.00,100.00 go drop PROCEDURE AVB_IniTable go /* author:nyb time :2005/04/22 fixtime : aim :转置行和列 input :@TableNane 执行： EXECUTE AVB_IniTable 'data2' */ Create PROCEDURE AVB_IniTable @TableNane varchar(128) AS DECLARE @string VARCHAR(8000) --1 创建View IF EXISTS( select * from sysobjects where xtype='V'and name ='V_Temp' ) Begin Drop view V_Temp End SELECT @string = ' Create view V_Temp as select * from ' + @TableNane EXECUTE (@string) IF EXISTS( select * from sysobjects where xtype='U'and name ='zzTemp' ) Begin Drop table zzTemp End DECLARE @ColumnName VARCHAR(200) DECLARE @ColumnStr VARCHAR(5000) select @ColumnStr= '' select @ColumnStr=@ColumnStr + quotename(rtrim(人员编号)) +'float NULL,' from V_Temp print @ColumnStr SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1) SELECT @string = 'CREATE TABLE zzTemp (列名 varchar(50) NULL,' + @ColumnStr + ') ON [PRIMARY]' print @string EXECUTE (@string) --2插入记录 DECLARE Column_cur SCROLL CURSOR FOR SELECT name FROM syscolumns WHERE ID=object_id(@TableNane) and name <>'人员编号' OPEN Column_cur FETCH FIRST FROM Column_cur into @ColumnName WHILE (@@fetch_status<>-1) BEGIN select @ColumnStr= '' if @ColumnName = '基本工资' select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(基本工资,0)) + ''',' from V_Temp else if @ColumnName = '奖金' select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(奖金,0)) + ''',' from V_Temp else if @ColumnName = '合计' select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(合计,0)) + ''',' from V_Temp SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1) select @string = 'insert into zzTemp values(''' + @ColumnName + ''',' + @ColumnStr +')' execute(@string) FETCH NEXT FROM Column_cur into @ColumnName END CLOSE Column_cur DEALLOCATE Column_cur go --察看结果 select * from data2 select * from zzTemp

=====================================================================
[c-sharp] view plain copy print ?
1. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
2. drop procedure [dbo].[p_zj]
3. GO
5. /*--行列互换的通用存储过程
7. 将指定的表,按指定的字段进行行列互换
9. --邹建 2004.04--*/
11. /*--使用示例
13. --测试数据
14. create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
15. insert 表 select '小说',38.0,59.2
16. union all select '散文',18.9,30.6
17. union all select '哲学',16.2,10.2
19. /*--要求转换结果
20. 性别小说散文哲学
21. ---- ----- ----- -----
22. 男性 38.0 18.9 16.2
23. 女性 59.2 30.6 10.2
25. （所影响的行数为 2 行）
26. --*/
28. --调用存储过程
29. exec p_zj '表','类别','性别'
31. --删除测试
32. drop table 表
33. --*/
34. create proc p_zj
35. @tbname sysname, --要处理的表名
36. @fdname sysname, --做为转换的列名
37. @new_fdname sysname='' --为转换后的列指定列名
38. as
39. declare @s1 varchar(8000),@s2 varchar(8000)
40. ,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
41. ,@i varchar(10)
42. select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
43. select @s1=@s1+',@'+@i+' varchar(8000)'
44. ,@s2=@s2+',@'+@i+'='''+case isnull(@new_fdname,'') when '' then ''
45. else @new_fdname+'=' end+''''''+name+''''''''
46. ,@s3=@s3+'
47. select @'+@i+'=@'+@i+'+'',[''+cast(['+@fdname+'] as varchar)+'']=''''''+replace(['+name+'],'''','''''''')+'''''''' from ['+@tbname+']'
48. ,@s4=@s4+',@'+@i+'=''select ''+@'+@i
49. ,@s5=@s5+'+'' union all ''+@'+@i
50. ,@i=cast(@i as int)+1
51. from syscolumns
52. where object_id(@tbname)=id and name<>@fdname
53. order by colid
55. select @s1=substring(@s1,2,8000)
56. ,@s2=substring(@s2,2,8000)
57. ,@s4=substring(@s4,2,8000)
58. ,@s5=substring(@s5,16,8000)
60. exec('declare '+@s1+'
61. select '+@s2+@s3+'
62. select '+@s4+'
63. exec('+@s5+')')
64. go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_zj] GO /*--行列互换的通用存储过程将指定的表,按指定的字段进行行列互换 --邹建 2004.04--*/ /*--使用示例 --测试数据 create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1)) insert 表 select '小说',38.0,59.2 union all select '散文',18.9,30.6 union all select '哲学',16.2,10.2 /*--要求转换结果性别小说散文哲学 ---- ----- ----- ----- 男性 38.0 18.9 16.2 女性 59.2 30.6 10.2 （所影响的行数为 2 行） --*/ --调用存储过程 exec p_zj '表','类别','性别' --删除测试 drop table 表 --*/ create proc p_zj @tbname sysname, --要处理的表名 @fdname sysname, --做为转换的列名 @new_fdname sysname='' --为转换后的列指定列名 as declare @s1 varchar(8000),@s2 varchar(8000) ,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000) ,@i varchar(10) select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0' select @s1=@s1+',@'+@i+' varchar(8000)' ,@s2=@s2+',@'+@i+'='''+case isnull(@new_fdname,'') when '' then '' else @new_fdname+'=' end+''''''+name+'''''''' ,@s3=@s3+' select @'+@i+'=@'+@i+'+'',[''+cast(['+@fdname+'] as varchar)+'']=''''''+replace(['+name+'],'''','''''''')+'''''''' from ['+@tbname+']' ,@s4=@s4+',@'+@i+'=''select ''+@'+@i ,@s5=@s5+'+'' union all ''+@'+@i ,@i=cast(@i as int)+1 from syscolumns where object_id(@tbname)=id and name<>@fdname order by colid select @s1=substring(@s1,2,8000) ,@s2=substring(@s2,2,8000) ,@s4=substring(@s4,2,8000) ,@s5=substring(@s5,16,8000) exec('declare '+@s1+' select '+@s2+@s3+' select '+@s4+' exec('+@s5+')') go

Oracle's:
[java] view plain copy print ?
1. 最近公司项目涉及到统计报表，有关交叉报表部分差不多都有行列转换需求，根据个人开发中的需求，总结了一些例子
3. 1.创建表
5. create table T1
6. (
7. PRODUCTID VARCHAR2(100),
8. NUM NUMBER
9. )
10. ;
12. 2.插入测试数据
14. insert into T1 (PRODUCTID, NUM)
15. values ('1', 3);
16. insert into T1 (PRODUCTID, NUM)
17. values ('2', 12);
18. insert into T1 (PRODUCTID, NUM)
19. values ('3', 24);
20. insert into T1 (PRODUCTID, NUM)
21. values ('4', 11);
22. insert into T1 (PRODUCTID, NUM)
23. values ('5', 51);
24. insert into T1 (PRODUCTID, NUM)
25. values ('6', 511);
26. commit;
28. 3.编写转换函数
30. CREATE OR REPLACE FUNCTION fn_rs_1
31. RETURN pkg_getrecord.myrctype
32. IS
33. s VARCHAR2 (4000);
34. CURSOR c1 IS
35. SELECT ',sum(case when productid!='|| productid || ' then num else 0 end)' || ' "产品' || productid|| '"' c2
36. FROM t1
37. group by productid;
38. r1 c1%ROWTYPE;
39. list_cursor pkg_getrecord.myrctype;
40. BEGIN
41. s := 'select 1 ';
42. OPEN c1;
43. LOOP
44. FETCH c1 INTO r1;
45. EXIT WHEN c1%NOTFOUND;
46. s := s || r1.c2;
47. END LOOP;
48. CLOSE c1;
49. s := s || ' from t1 g ';
50. dbms_output.put_line(s);
51. OPEN list_cursor FOR s;
52. RETURN list_cursor;
53. END fn_rs_1;
55. 4.java测试代码
57. public class Test {
58. private String db_url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
59. private String db_username = "test";
60. private String db_passwd = "test";
62. /**
63. * @param args
64. */
65. public static void main(String[] args) {
66. Test test=new Test();
67. try {
68. test.exec();
69. } catch (SQLException e) {
70. e.printStackTrace();
71. }
72. }
74. public void exec() throws SQLException {
75. Connection conn = getConnection();
76. if(conn==null){
77. throw new SQLException("database not connectioned");
78. }
79. CallableStatement cstmt = conn.prepareCall("{?=call fn_rs_1}");
80. cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
81. cstmt.execute();
82. ResultSet rs=(ResultSet)cstmt.getObject(1);
83. if(rs.next()){
84. for(int j=1;j<=rs.getMetaData().getColumnCount();j++){
85. System.out.print(rs.getMetaData().getColumnName(j)+"/t");
86. }
87. }
88. System.out.println("");
89. cstmt.execute();
90. rs=(ResultSet)cstmt.getObject(1);
91. while(rs.next()){
92. System.out.print(rs.getString(1)+"/t");
93. System.out.print(rs.getString(2)+"/t");
94. System.out.print(rs.getString(3)+"/t");
95. System.out.print(rs.getString(4)+"/t");
96. System.out.print(rs.getString(5)+"/t");
97. System.out.print(rs.getString(6)+"/t");
98. System.out.print(rs.getString(7)+"/t");
99. }
100. cstmt.close();
101. }
103. protected Connection getConnection() {
104. try {
105. Class.forName("oracle.jdbc.driver.OracleDriver");
106. return DriverManager.getConnection(db_url, db_username, db_passwd);
107. } catch (ClassNotFoundException e) {
108. e.printStackTrace();
109. } catch (SQLException e) {
110. e.printStackTrace();
111. }
112. return null;
113. }
115. }
118. 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jianping_shen/archive/2009/04/01/4041169.aspx

luyuncsd123

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL行列互换

面试的时候遇到的数据库SQL问题，没写过，然后研究了一下，现将结果记录下来，方便以后查询。题目1：将表tbltest1的行列互换表结构：student kemu fenshustudent1 语文 80 student1 数学 90 student1 英语 85 student2 语文 85 st
复制链接

扫一扫