如下表:
ID P_NAME P_TYPE P_CITY
-----------------------------------
1 P1 X SH
2 P2 X SH
3 P3 M BJ
4 P4 M SZ
5 P5 M BJ
6 P6 M SZ
如下输出结果:
P_NAME+P_TYPE P_CITY
-----------------------------------
P1:X|P2:X SH
P3:M|P5:M BJ
P4:M|P6:M SZ
就是两个字断P_NAME+P_TYPE 累加,中间用 ": "符号隔开,然后P_CITY同样的记录累加,中间 "| "符号隔开.
用一条语句实现?
------解决方案--------------------
create function dbo.fn_Merge(@P_CITY varchar(1000))
returns varchar(8000)
as
begin
declare @name varchar(8000)
set @name= ' '
select @[email protected]+ '| '+P_NAME+ ': '+P_TYPE from test where [email protected]_CITY
return stuff(@name,1,1, ' ')
end
go
create table test(ID int,P_NAME varchar(10),P_TYPE varchar(10),P_CITY varchar(10))
insert test select 1, 'P1 ', 'X ', 'SH '
union all select 2, 'P2 ', 'X ', 'SH '
union all select 3, 'P3 ', 'M ', 'BJ '
union all select 4, 'P4 ', 'M ', 'SZ '
union all select 5, 'P5 ', 'M ', 'BJ '
union all select 6, 'P6 ', 'M ', 'SZ '
select distinct [P_NAME+P_TYPE]=dbo.fn_Merge(P_CITY),P_CITY from test