参考如下例子:(转贴)
求一查询语句,所有列数据类型为varchar
列名 col1 col2 col3
A X n
A X v
A X a
A Y t
A Y j
......查询结果
A X n,v,a
A Y t,j
......
一条SQL语句能完成吗?--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
--测试--测试数据
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert 表 select 'A','X','n'
union all select 'A','X','v'
union all select 'A','X','a'
union all select 'A','Y','t'
union all select 'A','Y','j'
go--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
go--删除测试
drop table 表
drop function f_str/*-测试结果col1 col2 col3
---------- ---------- ------------
A X n,v,a
A Y t,j(所影响的行数为 2 行)
--*/
建表:create table tn2 (col1 varchar(10),col2 varchar(10),col3 varchar(10))输入:
insert into tn2 select
'A' ,'X' ,'n'
union select
'A' ,'X' ,'v'
union select
'A' ,'X' ,'a'
union select
'A' ,'Y' ,'t'
union select
'A' ,'Y' ,'j'
构造函数:create function f_try(@a varchar(10))
returns varchar(100)
as
begin
declare @b varchar(100)
set @b=''
select @b=@b+','+col3 from tn2 where col2= @a
set @b=reverse(right(@b,len(@b)-1))
return(@b)
end运行:
select col1,col2,dbo.f_try(col2) from tn2 group by col1,col2结果:
A X n,v,a
A Y t,j
求一查询语句,所有列数据类型为varchar
列名 col1 col2 col3
A X n
A X v
A X a
A Y t
A Y j
......查询结果
A X n,v,a
A Y t,j
......
一条SQL语句能完成吗?--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
--测试--测试数据
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert 表 select 'A','X','n'
union all select 'A','X','v'
union all select 'A','X','a'
union all select 'A','Y','t'
union all select 'A','Y','j'
go--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
go--删除测试
drop table 表
drop function f_str/*-测试结果col1 col2 col3
---------- ---------- ------------
A X n,v,a
A Y t,j(所影响的行数为 2 行)
--*/
建表:create table tn2 (col1 varchar(10),col2 varchar(10),col3 varchar(10))输入:
insert into tn2 select
'A' ,'X' ,'n'
union select
'A' ,'X' ,'v'
union select
'A' ,'X' ,'a'
union select
'A' ,'Y' ,'t'
union select
'A' ,'Y' ,'j'
构造函数:create function f_try(@a varchar(10))
returns varchar(100)
as
begin
declare @b varchar(100)
set @b=''
select @b=@b+','+col3 from tn2 where col2= @a
set @b=reverse(right(@b,len(@b)-1))
return(@b)
end运行:
select col1,col2,dbo.f_try(col2) from tn2 group by col1,col2结果:
A X n,v,a
A Y t,j
update table set vid=@vid,nr1=@nr1+nr1+',',nr2=@nr2+nr2+',',@vid=vid,@nr1=nr1,@nr2=nr2
where vid=@vid
select vid,max(nr1),max(nr2) from table
update table set @nr1=case vid when @vid then @nr1+nr1+',' else nr1 end ,nr2=case vid when @vid then @nr2+nr2+',' else nr2 end ,@vid=vid,nr1=@nr1,nr2=@nr2
求一查询语句,所有列数据类型为varchar
列名 col1 col2 col3
A X n
A X v
A X a
A Y t
A Y j
......查询结果
A X n,v,a
A Y t,j
......
一条SQL语句能完成吗?--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
--测试--测试数据
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert 表 select 'A','X','n'
union all select 'A','X','v'
union all select 'A','X','a'
union all select 'A','Y','t'
union all select 'A','Y','j'
go--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
go--删除测试
drop table 表
drop function f_str/*-测试结果col1 col2 col3
---------- ---------- ------------
A X n,v,a
A Y t,j(所影响的行数为 2 行)
--*/
建表:create table tn2 (col1 varchar(10),col2 varchar(10),col3 varchar(10))输入:
insert into tn2 select
'A' ,'X' ,'n'
union select
'A' ,'X' ,'v'
union select
'A' ,'X' ,'a'
union select
'A' ,'Y' ,'t'
union select
'A' ,'Y' ,'j'
构造函数:create function f_try(@a varchar(10))
returns varchar(100)
as
begin
declare @b varchar(100)
set @b=''
select @b=@b+','+col3 from tn2 where col2= @a
set @b=reverse(right(@b,len(@b)-1))
return(@b)
end运行:
select col1,col2,dbo.f_try(col2) from tn2 group by col1,col2结果:
A X n,v,a
A Y t,j