微软BI开拓者

要求：
1、使用基于集合的方式进行处理
2、不能使用表变量、临时表、while循环、自定义函数
3、可以使用CTE

用下列代码生成测试数据：
DECLARE @t TABLE (id int identity,Data VARCHAR(40) )
INSERT @t (Data) select 'XEDDDEFFE'
INSERT @t (Data) SELECT 'FWRRFXPPLW'

生成的示例数据如下：
id          Data
----------- ----------------------------------------
1          XEDDDEFFE
2          FWRRFXPPLW


生成下面的结果：
id          data                                    col
----------- ---------------------------------------- ------------
1          XEDDDEFFE                                XEDF
2          FWRRFXPPLW                              FWRXPL

DECLARE @t TABLE (id int identity,Data VARCHAR(40) )
INSERT @t (Data) select 'XEDDDEFFE'
INSERT @t (Data) SELECT 'FWRRFXPPLW';

with cte_r as
(
select a.id,a.Data,Char=substring(a.Data,b.number+1,1),b.number
from @t a inner join
master.dbo.spt_values b on b.type='P' and b.number<len(a.Data)
)
,
cte_r1 as
(
select id,Data,Char=cast(Char as varchar(40)),number from cte_r where number=0
union all
select a.id,a.Data,Char=cast(case when charindex(b.Char,a.Char)>0 then a.Char else a.Char+b.Char end as varchar(40)),b.number
from cte_r1 a inner join
cte_r b on a.id=b.id and a.number+1=b.number
)

select id,Data,Char from cte_r1 a
where not exists(select * from cte_r1 where id=a.id and number>a.number)
order by 1
--or
--select id,Data,Char from cte_r1 a
--where number=(select max(number) from cte_r1 where id=a.id)
--order by 1
组合字符串也可用xml实现拼接

DECLARE @t TABLE (id int identity,data VARCHAR(40) )
INSERT @t (Data) select 'XEDDDEFFE'
INSERT @t (Data) SELECT 'FWRRFXPPLW';
select * from @t
;WITH T AS
(  SELECT top 26 ROW_NUMBER() OVER (ORDER BY Object_ID) AS n
  FROM SYS.OBJECTS
),
T2 AS
( SELECT id,data,CHAR(64+N) AS M,N
  FROM @T JOIN T ON CHARINDEX(CHAR(64+N),data) > 0
)
SELECT id,data,replace(
                  (SELECT M as [data()] FROM T2 WHERE data = T3.data ORDER BY N FOR XML PATH('')
                    ),' ','') AS col
FROM @T T3

DECLARE @t TABLE (id int identity,Data VARCHAR(40) )
INSERT @t (Data) select 'XEDDDEFFE'
INSERT @t (Data) SELECT 'FWRRFXPPLW'
select * from @t


;WITH R AS
(
SELECT Data,0 AS i,CAST(Data AS VARCHAR(MAX)) AS NewData,CAST(LEN(Data) AS INT) AS iLen  FROM @t
UNION ALL
SELECT a.Data,b.i+1,SUBSTRING(b.NewData,1,i+1) + REPLACE(SUBSTRING(b.NewData,i + 1,LEN(b.NewData) - i),SUBSTRING(b.NewData,i + 1,1),'') AS NewData,CAST(LEN(NewData)- i AS INT)
FROM @t a
INNER JOIN r b ON a.Data = b.Data  and iLen > 0
)
SELECT Data,NewData FROM R WHERE iLen = 0