--1.创建一个合并的函数
create function fmerg(@a char(1),@b char(3))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'/'+colC from T_A where colA = @a and ColB = @b
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct ColA,ColB,dbo.fmerg(ColA,ColB) from T_A
[交流]行列转换
create table T_A(ColA char(1),colB char(3),ColC char(2))
insert T_A select 'A','001','A1'
union all select 'A','001','A2'
union all select 'A','001','A3'
union all select 'A','002','B1'
union all select 'B','003','C1'
union all select 'B','003','C2'
--1.创建一个合并的函数
create function fmerg(@a char(1),@b char(3))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'/'+colC from T_A where colA = @a and ColB = @b
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct ColA,ColB,dbo.fmerg(ColA,ColB) from T_A
/*结果
ColA ColB
---- ---- -------------
A 001 A1/A2/A3
A 002 B1
B 003 C1/C2(所影响的行数为 3 行)*/
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+rtrim(COLC) from T_A where COLA=@content AND COLB=@CONTENT2
select @str=right(@str,len(@str)-1)
return @str
end
goselect COLA,COLB,COLC=dbo.f_merg(COLA,COLB) from T_A group by COLA,COLB
Create function getstr(@id Nchar(100))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(4000),@分隔符 Nvarchar(10)
set @分隔符=N','
set @str=N''
select @str=@str+rtrim(Col)+@分隔符 from 表名
--------------------------------^^^^要相加的字段名
where 相加条件字段=@id
set @str=left(@str,len(@str)-1)落 ---却除最后一个分隔符
return @str
end
GO2,调用
select 条件字段,dbo.getstr(条件字段) from 表名 group by 条件字段
set @分隔符=N'/'
我早期在这里看到过,收藏过,但是现在弄不见了。
create function f_merg(@a char(1),@b char(3),@spch varchar(10))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+@spch+rtrim(colC) from T_A where colA= @a and ColB=@b
set @re=substring(@re,len(@spch)+1,8000)
return(@re)
End
go
create table T_A(ColA char(1),colB char(3),ColC char(2))
insert T_A select 'A','001','A1'
union all select 'A','001','A2'
union all select 'A','001','A3'
union all select 'A','002','B1'
union all select 'B','003','C1'
union all select 'B','003','C2'go
--创建一个合并的函数,可以自定义分隔符
create function f_merg(@a char(1),@b char(3),@spch varchar(10))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+@spch+rtrim(colC) from T_A where colA= @a and ColB=@b
set @re=substring(@re,len(@spch)+1,8000)
return(@re)
End
go--调用自定义函数得到结果,以/分隔
select ColA,ColB,ColC=dbo.f_merg(ColA,ColB,'/')
from T_A group by ColA,ColB--调用自定义函数得到结果,以,分隔
select ColA,ColB,ColC=dbo.f_merg(ColA,ColB,',')
from T_A group by ColA,ColBgo
--删除测试环境
drop table t_a
drop function f_merg/*结果
ColA ColB ColC
---- ---- ---------------
A 001 A1/A2/A3
A 002 B1
B 003 C1/C2(所影响的行数为 3 行)ColA ColB ColC
---- ---- ----------
A 001 A1,A2,A3
A 002 B1
B 003 C1,C2(所影响的行数为 3 行)
*/
create table T_A(ColA char(1),colB char(3),ColC char(2))
insert T_A select 'A','001','A1'
union all select 'A','001','A2'
union all select 'A','001','A3'
union all select 'A','002','B1'
union all select 'B','003','C1'
union all select 'B','003','C2'
goselect *,aa=cast('' as varchar(8000)) into #t from t_a order by cola,colb
declare @a char(1),@b char(3),@aa varchar(8000),@sp varchar(10)
set @sp='/' --定义分隔符
update #t set @aa=case when @a=cola and @b=colb then @aa+@sp+rtrim(colc) else rtrim(colc) end
,@a=cola,@b=colb,aa=@aaselect cola,colb,colc=max(aa) from #t group by cola,colb
go
--删除测试环境
drop table t_a,#t/*结果
ColA ColB ColC
---- ---- ---------------
A 001 A1/A2/A3
A 002 B1
B 003 C1/C2(所影响的行数为 3 行)ColA ColB ColC
---- ---- ----------
A 001 A1,A2,A3
A 002 B1
B 003 C1,C2(所影响的行数为 3 行)
*/