--各种字符串分函数

--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))

--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY  col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
    IF @col1=@col1_old
        SELECT @s=@s+','+CAST(@col2 as varchar)
    ELSE
    BEGIN
        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
        SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
    END
    FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO


/*==============================================*/


--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO

--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re varchar(100)
    SET @re=''
    SELECT @re=@re+','+CAST(col2 as varchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO

/*==============================================*/


--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
SELECT col1,col2=CAST(col2 as varchar(100)) 
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET 
    @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
    @col1=col1,
    col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO


/*==============================================*/

--3.3.4.1 每组 <=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(col2) as varchar)
        END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2      
---------- ----------
a          1,2
b          1,2
c          3
--*/

--3.3.4.2 每组 <=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=3 THEN ','
                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
            ELSE ''
        END
        +CASE 
            WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
            ELSE ''
        END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
--*/
GO

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (A int,B varchar(1))
insert into #T
select 1,'F' union all
select 2,'D' union all
select 3,'A' union all
select 3,'B' union all
select 3,'R' union all
select 4,'5'

-->2005
select A, B=(stuff((select '-'+B from #T where A=a.A for xml path('')),1,1,'')) from #T a group by A
/*
A           B
----------- -----------
1           F
2           D
3           A-B-R
4           5
*/

-->2000
declare @i int, @max int, @sql varchar(max)
select top 1 @i=1, @max=count(*) from #T group by A order by 2 desc
while @i<=@max
begin
    set @sql=isnull(@sql+'+max(case i when '+ltrim(@i)+' then ''-''+','max(case i when '+ltrim(@i)+' then ')+'B else '''' end)'
    set @i=@i+1
end
exec ('select A, B='+@sql+' from (select *,i=(select count(1) from #T where A=a.A and B<=a.B) from #T a) t group by A')
/*
A           B
----------- -----------
1           F
2           D
3           A-B-R
4           5
*/

create function f_str(@column1 int)
returns varchar(100)
as
begin
    declare @s varchar(200)
    select @s = isnull(@s+',','') + column2 from ta where column1 = @column1
    return @s
end
go

select column1,dbo.f_str(column1) as column2
from ta
group by column1

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (column1 int,column2 varchar(1))
insert into #T
select 1,'a' union all
select 1,'b' union all
select 2,'c' union all
select 2,'d' union all
select 2,'e'

-->2005
select column1, column2=(stuff((select ','+column2 from #T where column1=a.column1 for xml path('')),1,1,'')) from #T a group by column1
/*
column1     column2
----------- -----------
1           a,b
2           c,d,e
*/

-->2000
declare @i int, @max int, @sql varchar(max)
select top 1 @i=1, @max=count(*) from #T group by column1 order by 2 desc
while @i<=@max
begin
    set @sql=isnull(@sql+'+max(case i when '+ltrim(@i)+' then '',''+','max(case i when '+ltrim(@i)+' then ')+'column2 else '''' end)'
    set @i=@i+1
end
exec ('select column1, column2='+@sql+' from (select *,i=(select count(1) from #T where column1=a.column1 and column2<=a.column2) from #T a) t group by column1')
/*
column1     column2
----------- -----------
1           a,b
2           c,d,e
*/

create table tb (column1 int ,column2 varchar(10))
insert into tb select 1,'a' union all select 1,'b' union all select 2,'c' union all select 2,'d' union all select 2,'e'
go


create function dbo.f_str(@column1 varchar(10))
returns varchar(1000)
as
begin
   declare @str varchar(1000)
    set @str = ''
   select @str = @str+','+cast(column2 as varchar(10) ) from tb where column1= @column1
   return stuff(@str,1,1,'')
end

select distinct column1,dbo.f_str(column1) column2  from tb

drop table tb
drop function f_str

CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb 
SELECT 'a',1
UNION ALL SELECT 'b',1
UNION ALL SELECT 'c',2
UNION ALL SELECT 'd',2
UNION ALL SELECT 'e',2

GO
Declare @col2 int , @sql nvarchar(1000) 

---    表变量 
Declare @T Table (a int , b nvarchar(1000))

Declare MC Cursor For 
Select Distinct Col2 From tb 

Open MC 
Fetch next From MC Into @col2
While @@Fetch_Status = 0 
Begin
    Set @Sql = ''
    Select @Sql = @Sql+Case When @Sql='' Then '' Else ',' End + col1 From tb Where col2 = @col2
    Insert Into @T Values(@col2,@Sql)
    Fetch next From MC Into @col2
End

Close MC 
Deallocate MC

Select * From @T