CREATE FUNCTION GET_STRING(@ID INT) RETURNS VARCHAR(500) AS BEGIN DECLARE @NAME VARCHAR(500) SELECT @NAME=ISNULL(@NAME+',','')+NAME FROM TB WHERE ID=@ID RETURN @NAME END
select name, class=stuff((select ','+class from t1 where name=t.name for xml path('')),1,1,'') from t1 t group by name
人可能有很多,能不能在sql里一次查出来
见3楼,如果是sql2000的话用函数
-- ============================================= -- Author: T.O.P -- Create date: 20091128 -- Version: SQL SERVER 2000 -- ============================================= CREATE FUNCTION fn_getstr (@name varchar(4)) returns varchar(100) as begin declare @str nvarchar(100) select @str = isnull(@str+',','')+class from tb where name=@name return @strend goif object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(4),[class] varchar(4)) insert [tb] select '张三','数学' union all select '张三','语文' union all select '李四','化学' union all select '李四','物理'select name, dbo.fn_getstr(name) from [tb] group by namedrop table tb --测试结果: /* name ---- ---------------------------------------------------------------------------------------------------- 李四 化学,物理 张三 数学,语文(所影响的行数为 2 行)*/
---测试数据--- if object_id('[t1]') is not null drop table [t1] go create table [t1]([name] varchar(4),[class] varchar(4)) insert [t1] select '张三','数学' union all select '张三','语文' union all select '李四','化学' union all select '李四','物理'
--创建字符连接函数--- create function F_Str(@col varchar(20)) returns nvarchar(30) as begin declare @S nvarchar(30) select @S=isnull(@S+', ','')+class from t1 where name=@col return @S end---查询--- select name,dbo.f_str(name) as class from t1 group by name---结果--- name class ---- ------------------------------ 李四 化学, 物理 张三 数学, 语文(所影响的行数为 2 行)
/* -- Author:SQL77--RICHIE -- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB If object_id('TB') is not null Drop table TB Go Create table TB(name VARCHAR(10),CLASS VARCHAR(10)) Go Insert into TB SELECT '张三', '数学' UNION ALL SELECT '张三', '语文'UNION ALL SELECT '李四', '物理'UNION ALL SELECT '李四', '化学' Go --Start ALTER FUNCTION GET_STRING(@NAME VARCHAR(10)) RETURNS VARCHAR(50) AS BEGIN DECLARE @CLASS VARCHAR(50) SELECT @CLASS=ISNULL(@CLASS+',','')+NAME FROM TB WHERE NAME=@NAME RETURN @CLASS END GO Select NAME,DBO.GET_STRING(NAME) from TB GROUP BY NAME --Result: /*(所影响的行数为 4 行)NAME ---------- -------------------------------------------------- 李四 李四,李四 张三 张三,张三(所影响的行数为 2 行)*/ --End
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @NAME VARCHAR(500)
SELECT @NAME=ISNULL(@NAME+',','')+NAME FROM TB WHERE ID=@ID
RETURN @NAME
END
name,
class=stuff((select ','+class from t1 where name=t.name for xml path('')),1,1,'')
from
t1 t
group by
name
见3楼,如果是sql2000的话用函数
-- Author: T.O.P
-- Create date: 20091128
-- Version: SQL SERVER 2000
-- =============================================
CREATE FUNCTION fn_getstr
(@name varchar(4))
returns varchar(100)
as
begin
declare @str nvarchar(100)
select @str = isnull(@str+',','')+class from tb where name=@name
return @strend
goif object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(4),[class] varchar(4))
insert [tb]
select '张三','数学' union all
select '张三','语文' union all
select '李四','化学' union all
select '李四','物理'select name, dbo.fn_getstr(name) from [tb] group by namedrop table tb
--测试结果:
/*
name
---- ----------------------------------------------------------------------------------------------------
李四 化学,物理
张三 数学,语文(所影响的行数为 2 行)*/
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([name] varchar(4),[class] varchar(4))
insert [t1]
select '张三','数学' union all
select '张三','语文' union all
select '李四','化学' union all
select '李四','物理'
--创建字符连接函数---
create function F_Str(@col varchar(20))
returns nvarchar(30)
as
begin
declare @S nvarchar(30)
select
@S=isnull(@S+', ','')+class
from
t1
where
name=@col
return @S
end---查询---
select name,dbo.f_str(name) as class
from t1
group by name---结果---
name class
---- ------------------------------
李四 化学, 物理
张三 数学, 语文(所影响的行数为 2 行)
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(name VARCHAR(10),CLASS VARCHAR(10))
Go
Insert into TB
SELECT '张三', '数学' UNION ALL
SELECT '张三', '语文'UNION ALL
SELECT '李四', '物理'UNION ALL
SELECT '李四', '化学'
Go
--Start
ALTER FUNCTION GET_STRING(@NAME VARCHAR(10))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @CLASS VARCHAR(50)
SELECT @CLASS=ISNULL(@CLASS+',','')+NAME FROM TB WHERE NAME=@NAME
RETURN @CLASS
END
GO
Select NAME,DBO.GET_STRING(NAME) from TB GROUP BY NAME
--Result:
/*(所影响的行数为 4 行)NAME
---------- --------------------------------------------------
李四 李四,李四
张三 张三,张三(所影响的行数为 2 行)*/
--End