[SQLServer]数据库行列互换

发布时间：2020-12-12 15:38:01 所属栏目：MsSql教程来源：网络整理

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

?面试的时候遇到的数据库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

?=====================================================================

传说通用的，如下：

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,[合计] [numeric](19,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 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(奖金,' from V_Temp else if @ColumnName = '合计' select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(合计,' 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 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,@s4=substring(@s4,@s5=substring(@s5,16,8000) exec('declare '+@s1+' select '+@s2+@s3+' select '+@s4+' exec('+@s5+')') go

Oracle's:

最近公司项目涉及到统计报表，有关交叉报表部分差不多都有行列转换需求，根据个人开发中的需求，总结了一些例子 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!