写个函数
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
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的
请无视偶……