例如:假设一个表对应如下:BH(编号) XM(姓名)
001 张三
001 李四
001 小明
002 XXX
003 XXX想求出编号为001的所有姓名值赋值给变量XMSTR=张三,李四,小明我每次只能1先打开该表,2用取记录值的循环方式给XMSTR赋值.
这样很麻烦,能不能用一条SQL语句求出来!
请大家帮一下!
001 张三
001 李四
001 小明
002 XXX
003 XXX想求出编号为001的所有姓名值赋值给变量XMSTR=张三,李四,小明我每次只能1先打开该表,2用取记录值的循环方式给XMSTR赋值.
这样很麻烦,能不能用一条SQL语句求出来!
请大家帮一下!
insert into #
select '001','张三'
union all select '001','李四'
union all select '001','小明'
union all select '002','XXX'
union all select '003','XXX'declare @sql varchar(1000)
set @sql=''
select @sql=@sql+xm+',' from # where bh='001'
print left(@sql,len(@sql)-1)drop table #
INSERT INTO @tb
SELECT '001', '张三'
UNION ALL SELECT '001', '李四'
UNION ALL SELECT '001', '小明'
UNION ALL SELECT '002', 'XXX'
UNION ALL SELECT '003', 'XXX'DECLARE @BH VARCHAR(10)
DECLARE @RE VARCHAR(100)
SET @RE = ''
SET @BH = '001'
SELECT @RE = @RE + XM + ',' FROM @TB WHERE BH = @BH
SELECT LEFT(@RE,LEN(@RE)-1)
dm mc
1 ABC
2 CD
3 CE
3 CEB
1 A
结果:
dm mc
1 abc/a
2 cd
3 cd/ceb这要如何实现。
if object_id('pubs..t') is not null
drop table t
gocreate table t(dm int,mc varchar(10))
insert into t(dm,mc) values(1,'ABC')
insert into t(dm,mc) values(2,'CD')
insert into t(dm,mc) values(3,'CE')
insert into t(dm,mc) values(3,'CEB')
insert into t(dm,mc) values(1,'A')
go--创建一个合并的函数f_hb
if object_id('pubs..f_hb') is not null
drop function f_hb
gocreate function f_hb(@dm int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(mc as varchar) from t where dm = @dm
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct dm ,dbo.f_hb(dm) as mc from tdrop table t
drop function f_hb--结果
dm mc
----------- ------
1 ABC,A
2 CD
3 CE,CEB(所影响的行数为 3 行)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(xm as varchar(10)) from 表 where bh = @bh
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct bh ,dbo.f_hb(bh) as xm from t where bh = '001'
只需要下面几句就好了!
DECLARE @BH VARCHAR(100)
DECLARE @RE VARCHAR(100)
SET @RE = ''
SET @BH = 'SEA05060007'
SELECT @RE = @RE + sono + ',' FROM m_conso WHERE rdno=@bh
SELECT @re谢了各位!