Table数据
字段A 字段B 字段C
-----------------------------
A A_A 1
A A_A 2
A A_B 1
B B_A 1
B B_A 2
B B_A 3
B B_B 1
B B_B 2
B B_C 3输出结果
字段A 字段B 字段C
------------------------------
A A_A 1_2
A A_B 1
B B_A 1_2_3
B B_B 1_2
B B_C 3不使用循环,T-SQL怎么写???
字段A 字段B 字段C
-----------------------------
A A_A 1
A A_A 2
A A_B 1
B B_A 1
B B_A 2
B B_A 3
B B_B 1
B B_B 2
B B_C 3输出结果
字段A 字段B 字段C
------------------------------
A A_A 1_2
A A_B 1
B B_A 1_2_3
B B_B 1_2
B B_C 3不使用循环,T-SQL怎么写???
不过不知道你的版本够高不?
select
字段A ,字段B ,
字段C=stuff((select ','+字段C from tb where 字段A=t.字段A and 字段B=t.字段B for xml path('')),1,1,'')
from
tb t
group by
字段A ,字段B
字段A ,字段B ,
字段C=stuff((select '_'+字段C from tb where 字段A=t.字段A and 字段B=t.字段B for xml path('')),1,1,'')
from
tb t
group by
字段A ,字段B
if object_id('tempdb.dbo.#') is not null drop table #
create table #(字段A varchar(8), 字段B varchar(8), 字段C int)
insert into #
select 'A', 'A_A', 1 union all
select 'A', 'A_A', 2 union all
select 'A', 'A_B', 1 union all
select 'B', 'B_A', 1 union all
select 'B', 'B_A', 2 union all
select 'B', 'B_A', 3 union all
select 'B', 'B_B', 1 union all
select 'B', 'B_B', 2 union all
select 'B', 'B_C', 3
goselect 字段A, 字段B, 字段C = stuff((select '_' + rtrim(字段C) as [text()] from # where 字段A = a.字段A and 字段B = a.字段B for xml path('')) , 1, 1, '') from # as a group by 字段A, 字段B
/*
字段A 字段B 字段C
-------- -------- --------
A A_A 1_2
A A_B 1
B B_A 1_2_3
B B_B 1_2
B B_C 3
*/
go
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([A] varchar(1),[B] varchar(3),[C] int)
insert [TB]
select 'A','A_A',1 union all
select 'A','A_A',2 union all
select 'A','A_B',1 union all
select 'B','B_A',1 union all
select 'B','B_A',2 union all
select 'B','B_A',3 union all
select 'B','B_B',1 union all
select 'B','B_B',2 union all
select 'B','B_C',3
---查询---
SELECT A,B,
C=STUFF((SELECT '_'+LTRIM(C) FROM TB WHERE A=T.A AND B=T.B FOR XML PATH('')),1,1,'')
FROM TB T
GROUP BY A,B
---结果---
A B C
---- ---- -------------
A A_A 1_2
A A_B 1
B B_A 1_2_3
B B_B 1_2
B B_C 3(5 行受影响)
---创建字符连接函数---
create function [dbo].[f_str](@a varchar(20),@b varchar(20))
returns varchar(50)
as
begin
declare @s varchar(800)
select @s=isnull(@s+'_','')+ltrim(c)
from tb
where a=@a and b=@b
return @s
end---查询---
select a,b,dbo.f_str(a,b) as c from tb group by a,b---结果---
a b c
---- ---- --------------------------------------------------
A A_A 1_2
A A_B 1
B B_A 1_2_3
B B_B 1_2
B B_C 3(5 行受影响)