写个函数
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.GetSumDESC (@FUNCTION_ID Varchar(30))
RETURNS VarChar(40) AS
BEGIN
Declare @F VarChar(100)
Declare @V VarChar(100)
Select @F=''
Select @V=''
DECLARE myCUR CURSOR FOR
Select distinct DESCRIPTION from 表 where FUNCTION_ID=@FUNCTION_ID
OPEN myCUR --打开
FETCH NEXT FROM myCUR into @V; --下移一条记录
WHILE @@FETCH_STATUS=0 and Len(@F)<40
BEGIN
Select @F=(@F + @V + '--')
FETCH NEXT FROM myCUR into @V; --下移一条记录
END
CLOSE myCUR --释放
DEALLOCATE myCUR
Return @F
END GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO查询方法
select FUNCTION_ID,GetSumDESC(FUNCTION_ID) as SumDESC from 表 group by FUNCTION_ID
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.GetSumDESC (@FUNCTION_ID Varchar(30))
RETURNS VarChar(40) AS
BEGIN
Declare @F VarChar(100)
Declare @V VarChar(100)
Select @F=''
Select @V=''
DECLARE myCUR CURSOR FOR
Select distinct DESCRIPTION from 表 where FUNCTION_ID=@FUNCTION_ID
OPEN myCUR --打开
FETCH NEXT FROM myCUR into @V; --下移一条记录
WHILE @@FETCH_STATUS=0 and Len(@F)<40
BEGIN
Select @F=(@F + @V + '--')
FETCH NEXT FROM myCUR into @V; --下移一条记录
END
CLOSE myCUR --释放
DEALLOCATE myCUR
Return @F
END GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO查询方法
select FUNCTION_ID,GetSumDESC(FUNCTION_ID) as SumDESC from 表 group by FUNCTION_ID
解决方案 »
- 怎样输出怎样'a','b','c','d'的格式
- 出现乱码问题?
- 求一条SQL语句。。
- unix下备份出来的oracle10.2的库文件在gz后,解压缩后的DMP文件可以还原到windows系统的ORACLE库不
- 关于作业执行的问题。
- 请问登陆OEMC时的managment server是什么?
- 如何学习oracle
- 我的oracle中有两个数据库,请问imp数据的时候怎么指定是导入哪个库呢?
- 关于数据库性能,索引未何不起作用,我用语句跟踪,发现2语句用查询扫描,没有使用索引,为何
- Oracle 8i 客户端的中文问题。
- 英数字检测
- 请问在orcale 8i 中用什么字段类型保存年月日时分秒的,急!在线等!!
SQL> select job,ename from emp;JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMSJOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER已选择14行。SQL> create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return||str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /函数已创建。SQL> select f_ename(job) from (
2 select distinct job from emp);F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNERSQL>
SQL> select job,f_ename(job) from (
2 select distinct job from emp);JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
以为是SQL server的
请无视偶……