需求贴:
http://topic.csdn.net/u/20120204/10/8b902fd2-8909-4ed9-b534-4a1a72454eff.html#r_77443331
需求简介:
比如:YYYYN, 结果处理为:人1,人2,人3,人4
分析:
貌似就是一个字符串的替换,如果是'Y'替换成'人'+Y的所在位置的编号。
解决方案:
declare @T table (id int,col varchar(5))
insert into @T
select 1,'YYYYN' union all
select 2,'YNNNY' union all
select 3,'NNNYN' union all
select 4,'YYNYN'
--1.第一种方法:case when
--最直观的处理方式
select id,col=left(col,len(col)-1) from
(
select id,col=
case when left(col,1)='Y' then '人1,' else '' end+
case when substring(col,2,1)='Y' then '人2,' else '' end+
case when substring(col,3,1)='Y' then '人3,' else '' end+
case when substring(col,4,1)='Y' then '人4,' else '' end+
case when substring(col,5,1)='Y' then '人5,' else '' end
from @T
) a
/*
id col
----------- --------------------
1 人1,人2,人3,人4
2 人1,人5
3 人4
4 人1,人2,人4
*/
--2.使用自定义函数
--创建函数
create function fn_GetPersonCount
(
@str varchar(5)
)
returns varchar(20)
as
begin
declare @result varchar(20) set @result=''
declare @i int set @i=0
while(charindex('Y',@str)>0)
begin
set @result=@result+'人'+ltrim(charindex('Y',@str)+@i)+','
set @str=right(@str,len(@str)-charindex('Y',@str))
set @i=@i+1
end
set @result=left(@result,len(@result)-1)
return @result
end
declare @T table (id int,col varchar(5))
insert into @T
select 1,'YYYYN' union all
select 2,'YNNNY' union all
select 3,'NNNYN' union all
select 4,'YYNYN'
select id,col=dbo.fn_GetPersonCount(col) from @T
/*
id col
----------- --------------------
1 人1,人2,人3,人4
2 人1,人5
3 人4
4 人1,人2,人4
*/
--3.合并列值
declare @T table (id int,col varchar(5))
insert into @T
select 1,'YYYYN' union all
select 2,'YNNNY' union all
select 3,'NNNYN' union all
select 4,'YYNYN'
;with maco as
(
select a.*,'人'+ltrim(b.number+1) as c1 from @T a,master..spt_values b
where b.type='p' and b.number <5 and substring(col,0+right(number+1,1),1)='Y'
)
select id, col=
stuff((select ','+c1 from maco t where id=maco.id for xml path('')), 1, 1, '')
from maco group by id
/*
id col
----------- --------------------
1 人1,人2,人3,人4
2 人1,人5
3 人4
4 人1,人2,人4
*/