一个表结构如下
Field_A Field_B
1 A
1 B
2 C
2 D
2 E
3 F
4 G
1 H
如何产生如下结果
1 a,b,H
2 c, d, e
3 f
4 g
------解决方案--------------------
参照：
使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a ',1
UNION ALL SELECT 'a ',2
UNION ALL SELECT 'b ',1
UNION ALL SELECT 'b ',2
UNION ALL SELECT 'b ',3
GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re= ' '
SELECT @[email protected]+ ', '+CAST(col2 as varchar)
FROM tb
WHERE [email protected]
RETURN(STUFF(@re,1,1, ' '))
END
GO
--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO
------解决方案--------------------
create table testa(Field_A int,Field_B char(1))
insert into testa
select 1, 'A '
union all select 1, 'B '
union all select 2, 'C '
union all select 2, 'D '
union all select 2, 'E '
union all select 3, 'F '
union all select 4, 'G '
union all select 1, 'H '
create function f_testa(@a int)
returns varchar(255)
as
begin
declare @s varchar(255)
set @s= ' '
select @[email protected]+Field_B+ ', ' from testa where [email protected]
return left(@s,len(@s)-1)
end
select Field_A,dbo.f_testa(Field_A) as Field_B from testa group by Field_A
------解决方案--------------------
create table testa(Field_A int,Field_B char(1))
insert into testa
select 1, 'A '
union all select 1, 'B '
union all select 2, 'C '
union all select 2, 'D '
union all select 2, 'E '
union all select 3, 'F '
union all select 4, 'G '
union all select 1, 'H '
create function f_testa(@id int)
returns varchar(255)
as
begin
declare @s varchar(255)
set @s= ' '
select @[email protected]+ ', '+Field_B from testa where [email protected]
select @s = stuff(@s,1,1, ' ')
return @s
end
select Field_A,dbo.f_testa(Field_A) as Field_B from testa group by Field_A
------解决方案--------------------
--带符号合并行列转换
--有表t，其数据如下：
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1
create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
go
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
--创建一个合并的函数