需求贴:
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
- */